simplifying killing spids because you really ought to

Wow, nearly two months have passed since my last post. Just goes to show how quickly things move on if you get distracted. I’ve been finding it a real problem of late to get the time to update the blog, since I know that when ever I do have something to write, it is going to take up a sizeable portion of time that I do not currently have. I am guessing the only way around this is to keep it short or have regular update slots set aside in my working schedule. Perhaps the latter idea is the best solution.

Moving swiftly on, one problem that I have found particulary annoying when performing any database operation that requires connections to it to be disconnected, such as setting it offline or a restoring over the top of itself is getting rid of them. Obviously this can be achieved by listing the current spids connected to the database and performing a kill for each spid. This can be quite laborious and obvious does not address any spids coming live whilst you are busy killing the existing ones, so you can end up going around in circles.

My solution to this is a script to effectively automate this manual process so that whilst the SET OFFLINE or RESTORE operation or such like are ticking away the procedure can be run specifying the name of the database and hey presto all live connections to this database are killed off one by one. The clever part (imo) to this script is the way in which it doesn’t attempt to block kill a batch of spids at the same time which might not be desirable should one of these spids close and be reused, but instead takes the first spid in the list and kills that and so on until no more spids are active for the specified database.

Obviously it is possible when altering a database to specify the option “WITH ROLLBACK IMMEDIATE” which has the same effect, but this option is not available for every situation and operations.

Anyway here is the stored procedure main body :-

@dbname sysname, –required option (the database from which to kill of any connected user based spids)
@help bit = 0 –optional option (view help)
WITH ENCRYPTION
AS
SET NOCOUNT ON

IF @dbname NOT IN (SELECT name FROM master..sysdatabases) AND LEN(@dbname) = 0
BEGIN
PRINT @dbname + ‘ database specified cannot be found.’
PRINT ”
RETURN
END

IF LOWER(@dbname) IN (‘master’,’tempdb’)
BEGIN
PRINT @dbname + ‘ invalid operation on database ‘ + @dbname + ‘ specify different database.’
PRINT ”
RETURN
END

SET NOCOUNT ON
DECLARE @spid SMALLINT
DECLARE @str VARCHAR(255)

–just select the last spid and kill it and loop around until no more spids
PRINT ‘Killing user processes in database ‘ + @dbname
PRINT ‘Executing from SPID ‘ + CONVERT(VARCHAR,@@SPID)
WHILE EXISTS(SELECT TOP 1 spid FROM master..sysprocesses WHERE dbid = DB_ID(@dbname) and spid > 50 and spid @@SPID) –user spids are 51 and higher
BEGIN
SELECT TOP 1 @spid = spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname) and spid > 50 and spid @@SPID –user spids are 51 and higher

SET @str = ‘KILL ‘ + CONVERT(VARCHAR, @spid)

PRINT @str;

EXEC(@str)
END

PRINT ‘Complete.’

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.