Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Code to Kill Stale Processes Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 11:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1487939
Posted Friday, August 23, 2013 11:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
AndrewSQLDBA (8/23/2013)
Why would I not want too? They are taking up memory and cpu


An 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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1487952
Posted Wednesday, August 28, 2013 6:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 883, Visits: 2,807
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;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1489176
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse