Archive: Posts Tagged ‘SQL Server 2005’

Stop All Processes Accessing a SQL Database.

No comments June 7th, 2010

Ever try to exclusively lock a database but something else is already is still utilizing it, well try this:

USE master

-- Declare working variables
DECLARE @vcdbname1 VARCHAR(50)
DECLARE @vcdbname2 VARCHAR(50)

DECLARE @spid INT,
@str VARCHAR(255)

-- Set the input database names here - if you don't have a db set it to NULL

SET @vcdbname1 = 'MOSS_CONTENT_SSP_ADMIN'
SET @vcdbname2 = 'MOSS_SSP_DB'

SET NOCOUNT ON

-- Create the cursor from the sysprocesses table to get the ids of the databases

DECLARE UsersCur CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE (db_name(dbid) = @vcdbname1 AND @vcdbname1 IS NOT NULL)
OR (db_name(dbid) = @vcdbname2 AND @vcdbname2 IS NOT NULL)

-- Open the cursor
OPEN UsersCur

-- Get the first row (database id)
FETCH NEXT FROM UsersCur INTO @spid

WHILE @@fetch_status <> -1
BEGIN

IF @@fetch_status = 0
BEGIN

SET @str = 'kill ' + CONVERT(VARCHAR, @spid)
-- uncomment the row to test (it will show you the commands it will run
PRINT @str

-- comment the above and uncomment this to kill the sessions
EXEC (@str)

END

-- Get the next row
FETCH NEXT FROM UsersCur INTO @spid
-- you are done
END

-- clean up your variables
CLOSE UsersCur
DEALLOCATE UsersCur