Kill SQL Server Process Ids (spids)

 

Problem

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?

Solution

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 GO SET NOCOUNT ON -- 1 - Variable Declaration DECLARE @DBID int 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) SELECT spid FROM Master.dbo.sysprocesses WHERE DBID NOT IN (1,2,3,4) -- Master, Tempdb, Model, MSDB AND spid > 50 AND <> @@spid ORDER BY spid DESC -- 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 BEGIN -- 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 -- SELECT @CMD1 EXEC (@CMD1) -- 3g - Descend through the spid list SELECT @SpidListLoop = @SpidListLoop - 1 END SET NOCOUNT OFF GO