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 
GO 

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:

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s