Saturday, June 16, 2007

Terminate SQL Server processes for a database

I have come accross the same problem several times now and have had to google for the answer. This one is the best one I have found so far. I take no credit for the script but I have lost the original link so my appologies to the original author. If you read this please let me know so I can supply credit.

declare @spidstr varchar(8000)

select @spidstr=coalesce(@spidstr,'')+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses WHERE dbid=db_id('database name')

if @spidstr != '' exec (@spidstr)

The script can actually be put on a single line which makes running it from Ant (etc) very easy.

Quite often as a developer I need to rebuild my test database. However it is often the case that there are processes still running. It would be nice to track them all down but this is time consuming and it is often easier just to kill the processes in the database server and rebuild then retest.

No comments: