Brian Hartsock's Blog

Kill SQL Connections by Login

by bhartsock on Mar.25, 2009, under Uncategorized

Ever get this error message?

Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.

You can’t do a restore will connections to that database still exist!

If you have jobs that do restores of databases for one reason or another, I bet you have seen this message. I have a job to do a nightly restore of a backup, but some users just never disconnect even though I continually shake my finger. So what do I do? I kill all the processes from problem users before starting the restore.

 
--Create the cursor
declare mycursor cursor
FOR
 
-- Query for processes by login and database
SELECT spid, Loginame
FROM master..sysProcesses
WHERE Loginame='someuser' AND dbid=db_id('somedb')
 
 
open mycursor
 
declare @spid int, @loginame varchar(255), @cmd varchar(255)
 
-- Loop through the cursor, killing each process
Fetch NEXT FROM MYCursor INTO @spid, @loginame
While (@@FETCH_STATUS <> -1)
begin
	-- I don't really know why this is necasary, but it is.
	SELECT @cmd = 'kill ' + cast(@spid AS varchar(5))
	exec(@cmd)
 
	Fetch NEXT FROM MYCursor INTO @spid, @loginame
end
 
close mycursor
deallocate mycursor
go

I am no TSQL expert, so kudos to this article for helping me figure out why kill @spid wasn’t working.

Post to Twitter Post to Digg Post to Facebook Post to Reddit

:

1 Comment for this entry

  • SQL Tutorials

    You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

Leave a Reply