SQL Code to Kill Stale Processes
-
Happy Friday Everyone
I am working on some things today. I was thinking it would be good to kill all the stale connections that are over a certain age. I am not sure how to go about this however. I used to have some code that would do this, but I cannot find it. Can anyone assist me in finding the code to perform this task?
Thank you in advance for all your assistance, suggestions and comments
Andrew SQLDBA
-
Why?
What problems are they causing?
Do you know what they were doing?
Do you know if the applications can handle a terminated connection?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
There's a good post by Dave Smith below:
http://forums.databasejournal.com/showthread.php?3090.html
I think this will help with what you need
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Hey Gila
The application is not closing them. I need to kill them if they are a couple days old. Also, there are some users that connect using SSMS that leave it open after not logging out correctly.
Thanks
Andrew
-
AndrewSQLDBA (8/23/2013)
Hey GilaThe application is not closing them. I need to kill them if they are a couple days old.
Why? What problem are they causing? What effect will it have on the app if you do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It will not affect the app at all.
Andrew
-
So why close them?
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
There are some users that connect using SSMS that leave it open after not logging out of the remote session correctly. That is the biggest thing I want to kill
Andrew SQLDBA
-
Why do you want to kill those? Why bother?
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
Why would I not want too? They are taking up memory and cpu
Andrew
-
I have also seen cases where uses are testing their procedures/queries and leave their session open...depending on the stuff they have been running, it can take up GB's in TempDB - so I kill them as well
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
AndrewSQLDBA (8/23/2013)
Why would I not want too? They are taking up memory and cpuAn idle thread consumes no CPU. It'll have a 2MB thread stack (or 4MB, can't recall), no other memory usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This code will kill all processes who do not have an active request, who's last request was more than 24 hours ago and are connecting from management studio. You can adjust the WHERE clause to suit your needs.
DECLARE @SPID INT,@SQL NVARCHAR(MAX);
DECLARE CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT
s.session_id
FROM
sys.dm_exec_sessions s
LEFT JOIN
sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE
r.session_id IS NULL
AND program_name LIKE '%Microsoft SQL Server Management Studio%'
AND last_request_start_time < DATEADD(HOUR, -24, GETDATE());
OPEN CUR;
FETCH CUR INTO @SPID;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'KILL ' + CAST(@SPID AS VARCHAR(5));
BEGIN TRY
EXEC sp_executesql @SQL;
PRINT 'SUCCESS: ' + @SQL;
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + @SQL;
END CATCH;
FETCH CUR INTO @SPID;
END;
CLOSE CUR;
DEALLOCATE CUR;
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply