Learn about Die Sucker - Killing SQL Server Process Ids (spids)

get the feedFirst time visiting? Wanna stay informed and be the first to know when we publish new content? Make sure you subscribe to the RSS feed before you leave!
subscribe to our newsletterDo you like our posts? GlobalGuideLine.com newsletters with the best websites organized by categories and other news.

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 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 scriptstored 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
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

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.

Stay tuned for another tip on situational alternatives to issuing the KILL command.

Share Your Ideas and Suggestions About Die Sucker - Killing SQL Server Process Ids (spids)

Webmaster Said:

Webmaster review about this article.

Comments will be published after webmaster review. You are welcome to share your ideas, experiences, suggestions and questions, but please be respectful of others when commenting. Insulting, self-promotional, website promotion, SEO Techniques, SMS-style and off-topic comments will not be published. Share your comments regarding Die Sucker - Killing SQL Server Process Ids (spids)
Thank you.

Rate this article Please...

Feel Free To Leave Your Comment

Latest MS SQL Server Articles Research and Updates