SupportSQL

Problem:

Say goodbye to the frustrations over getting quick, exclusive access to a database during the restore or repair of that database while end users keep gaining access without cease. This sp will get the database into single-user mode fast after diligently 86ing all spids.  The issue is that you can kill and kill spids but other users can still get into the db until the ‘set single user mode’ is executed. A looping kill statement with a poised ‘set single_user’ statement works nice. For heavy traffic environments, increase the max loop limit and shorten the wait time value.

Basic logic:

1. kill all users in the db

2. verify all are killed

3. re-kill if needed (up to 10 retries a few seconds apart)

4. immediately set the database in single user mode for the current query window (where restores/repairs can be done)

5. provide the statement to set database back to multi-user mode if needed.

(note - this will only execute specifically for users in the ACL list (see the code below) and will not work on system databases)

The syntax for the procedure is:

use master; EXEC gp_DBA_KillForceMode @DBName = ‘mydb’

The output is:

killing spids…try #1

kill 60; kill 61; kill 62;

No spids in db. Setting mode…

DB mode changed.

User access mode is now: SINGLE_USER

This query window is the only user allowed in the database. If this is closed another user would be allowed to access the database exclusively. Recommended to not close this connection/window until returning db to multi_user mode.

—Use this statement to return to multi_user mode:

ALTER DATABASE mydb SET multi_user

Here’s the code:

CREATE PROCEDURE [dbo].[gp_DBA_KillForceMode] @DBName varchar(255)
AS 
SET NOCOUNT ON

DECLARE @strspid varchar(8000)

DECLARE @k smallint

DECLARE @sql nvarchar(500)

SET @k = 0

SET @strspid = ''

DECLARE @l int

SET @l = 10

IF db_id(@DBName) IS  NULL 
    BEGIN

        PRINT '@DBName not found!'

        RETURN

    END

  
IF db_id(@DBName) < 4 
    BEGIN

        PRINT 'Connections to system databases cannot be killed'

        RETURN

    END

  
IF system_user NOT IN ('')    --PUT VALID LOGINS AUTHORIZED TO EXECUTE THIS 
    BEGIN

        PRINT 'THIS AUTHENTICATED USER IS NOT ACL INCLUDED - ABORT'

        RETURN

    END

IF EXISTS ( SELECT
                dbid
            FROM
                master..sysprocesses
            WHERE
                spid = @@spid
                AND dbid = db_id(@dbname) ) 
    BEGIN 

        PRINT 'This is running as spid: ' + cast(@@spid AS varchar)

        PRINT 'You are in @DBName. Change DB and try again - i.e. use master'

        RETURN

    END

  
killem:

PRINT 'killing spids...try #' + convert(varchar(3), 11 - @l)

  
SELECT
    @strspid = coalesce(@strspid, ',') + 'kill ' + convert(varchar, spid) + '; '
FROM
    master..sysprocesses
WHERE
    dbid = db_id(@DBName)
    AND spid <> @@spid

PRINT @strspid

  
IF len(@strspid) > 0 
    BEGIN

        EXEC (@strspid)

  
        SELECT
            @k = count(1)

        SELECT
            *
        FROM
            master..sysprocesses
        WHERE
            dbid = db_id(@DBName) 

    END

IF EXISTS ( SELECT
                db_name(dbid)
               ,*
            FROM
                master..sysprocesses
            WHERE
                dbid = db_id(@DBName)
                AND spid <> @@spid ) 
    BEGIN

        SELECT
            @l = @l - 1

        IF @l < 1 
            BEGIN

                PRINT 'Max retries reached.'

                RETURN

            END

        ELSE 
            BEGIN

                PRINT 'waiting a few...'

                WAITFOR DELAY '00:00:03'

                GOTO killem               

            END

    END

PRINT 'No spids in db. Setting mode...'

  
SELECT
    @sql = 'use master; ALTER DATABASE ' + @DBName + ' SET single_user;'

  
EXEC sp_executesql @sql

  
IF @@error = 0 
    PRINT 'DB mode changed.'

SELECT
    @sql = (SELECT
                user_access_desc
            FROM
                sys.databases d
            WHERE
                d.NAME = @dbname)

PRINT 'User access mode is now: ' + @sql

  
IF @sql = 'SINGLE_USER' 
    BEGIN

        PRINT 'This query window is the only user allowed in the database. If this is closed another user would be allowed to access the database exclusively. Recommended to not close this connection/window until returning db to multi_user mode.'

        PRINT '--Use this statement to return to multi_user mode:'

        PRINT 'ALTER DATABASE ' + @DBName + ' SET multi_user'

    END

ELSE 
    BEGIN

        PRINT 'Failed to set mode single_user! Database mode not changed.'    

    END

—by Alek Kirstein – SupportSQL.com