Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Kill SQL Server Process Ids (spids)

By:   |   Last Updated: 2007-05-18   |   Comments (6)   |   Related Tips: More > Scripts

I have noticed some of my processes are failing because spids are already connected to the database.  This happens specifically when I need to a restore database.  I catch this problem pretty quick when I am working on it during the data and can fix it, but during nightly processing existing spids become problematic.  I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance Plans.  I have found this issue in my logs specifically related to performing integrity checks (DBCC CHECKDB ('YourDatabaseName') REPAIR_FAST) when the database needs to be in single user mode before the integrity check commands run.  How can I kill these spids prior to running my processes?

Killing the spids is the process that needs to occur prior to issuing DBCC CHECKDB (when repairing) or performing the database restore process.  Killing the spids can be accomplished by adding another step to your SQL Server Agent Jobs or in your script\stored procedure calling the code below to perform the KILL  process prior to executing code that needs exclusive use of the database.

SQL Server 2000 and SQL Server 2005

USE Master


-- 1 - Variable Declaration
DECLARE @CMD1 varchar(8000)
DECLARE @spidNumber int
DECLARE @SpidListLoop int
DECLARE @SpidListTable table
(UIDSpidList int IDENTITY (1,1),
SpidNumber int)

-- 2 - Populate @SpidListTable with the spid information
INSERT INTO @SpidListTable (SpidNumber)
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) -- Master, Tempdb, Model, MSDB
AND spid > 50
AND <> @@spid


-- 3b - Determine the highest UIDSpidList to loop through the records
SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

-- 3c - While condition for looping through the spid records
WHILE @SpidListLoop > 0

-- 3d - Capture spids location
SELECT @spidNumber = spidnumber
FROM @spidListTable
WHERE UIDspidList = @SpidListLoop

-- 3e - String together the KILL statement
SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS varchar(5))

-- 3f - Execute the final string to KILL the spids

-- 3g - Descend through the spid list
SELECT @SpidListLoop = @SpidListLoop - 1


Next Steps

  • Check your Database Maintenance Plan logs or SQL Server Agent Job Step History to determine when existing spids are preventing your automated processes from succeeding.
  • Depending on the user and/or automated processes that are running dictates how the scripts above should be modified to meet your needs.
  • Although the scripts above are beneficial for you to execute your process, you may also need to consider the other processes that are running and determine if 1 or more of the processes need to be executed at another time period for all of the processes to complete in a reasonable time period.  For example, you may not want to kill a process at 4:00 AM that needs 4 hours to complete and must be finished by 8:00 AM.  In this circumstance, re-scheduling the process may be a better bet.
  • Check out these related tips from MSSQLTips.com:
  • Stay tuned for another tip on situational alternatives to issuing the KILL command.

Last Updated: 2007-05-18

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, January 09, 2019 - 4:07:21 AM - sheikvara Back To Top

Hi manonbar,

Your suggestion is right.

Is the one line that is in error

Before AND <> @@spid

After changing AND spid <> @@spid

Script is working or remove that error one line also script executing successfully.



Thursday, February 03, 2011 - 1:10:12 PM - Jeremy Kadlec Back To Top


Gotcha.  Ken's tip is pretty good.  He has a few great examples.  He is a sharp DBA.

Thank you,
Jeremy Kadlec

Thursday, February 03, 2011 - 12:09:13 PM - TIm Boesken Back To Top

Never thought to use COALESCE. Great find. Now you went and made my agenda for the day go out the window as I play with that command more :-) 

Thursday, February 03, 2011 - 11:48:05 AM - Jeremy Kadlec Back To Top


Thank you for the post.

Here is another alternative as well to kill spids - http://www.mssqltips.com/tip.asp?tip=1521.

Thank you,
Jeremy Kadlec

Thursday, February 03, 2011 - 9:22:26 AM - TIm Boesken Back To Top

Before restoring:

Alter database <blah> Set Restricted_User With Rollback Immediate

Restore Database.


Monday, September 14, 2009 - 6:38:36 AM - manonbar Back To Top

thanks, this tip was a great help.

One question,

Is the one line that is in error

AND <> @@spid

suppose to be AND spid <> @@spid or for any reason another field? It works fine with this modification, but just want to make sure not doing any unnecessary kills in there.


Learn more about SQL Server tools