Kill connections

Here’s a handy piece of code to put before a backup or snapshot-restore, where you need to kill current connections …

--first remove any connections 
USE master 

DECLARE @kill varchar(8000) = ''; 

SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';' 
FROM master..sysprocesses 
WHERE dbid=db_id('SomeDatabaseName') 
AND spid>50; 

EXEC (@kill);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s