MS SQL – find and kill session

1 Apr

You can use the following query to check online session and kill:

sp_who

 

Kill command with spid:

kill <spid>

 

Another way to clean up old sessions – find all session which is idle more than 60 minutes:

DECLARE @v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status=’sleeping’
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@@spid

OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT ‘KILLing ‘+CONVERT(VARCHAR,@v_spid)+’…’
EXEC(‘KILL ‘+@v_spid)
FETCH NEXT FROM c_Users INTO @v_spid
END

CLOSE c_Users
DEALLOCATE c_Users

 

 

Read more

http://www.connectsql.com/2010/12/sql-server-find-and-kill-specific-user.html

http://msdn.microsoft.com/en-us/library/ms173730.aspx

http://forums.asp.net/t/1026195.aspx?How+can+I+kill+sleeping+processes+in+SQL+Server+

 http://stackoverflow.com/questions/1248423/how-to-see-active-sql-server-connections

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: