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.
April 30th, 2009 on 10:51 pm
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.