Πώς να “σκοτώσω” τα ανενεργά connections στον SQL Server
Saturday 26 September 2009
Αφορμή για αυτό το post είναι ο Blackman. Σε μια ωραία συζήτηση που είχαμε μου εξέφρασε την επιθυμία για αυτό επειδή μια εφαρμογή που έχει του αφήνει ανοικτά sessions.
Σε αυτό το σημείο θα πρέπει να αναφέρω ότι κάτι τέτοιο μπορεί να συμβεί είτε διότι η εφαρμογή δεν έχει γραφτεί σωστά, είτε έπειδή ο σταθμός εργασίας που έχει ανοίξει το session σταμάτησε να λειτουργεί είτε η εφαρμογή σταμάτησε απότομα.
Ο SQL Server βέβαια έχει τους μηχανισμούς για να τα “σκοτώνει” αυτόματα αλλά απαιτείται να πειράξεις registry (http://msdn.microsoft.com/en-us/library/aa275788(SQL.80).aspx) αν θέλεις να γίνεται σε πιο σύντομο διάστημα. Όμως όπως θα δείτε και στο link που σας έδωσα στην ουσία πειράζει το πρωτόκολο επικοινωνίας, και αυτό ίσως να μην το θέλεις αν έχεις και άλλα services.
Εαν πάντως δεν θέλεις να κάνεις την παραπάνω αλλαγή σας παρέχω 2 τρόπους για να κάνετε kill τα idle sessions.
ΤΡΟΠΟΣ Α.
Αυτό ο τρόπος “σκοτώνει” όλα τα idle session σε όλες τις databases εκτός από τις system databases και το session με το οποίο κάνουμε την εκτέλεση της εργασίας.
Για να το κάνετε αυτό πρέπει πρώτα να πάρετε τον παρακάτω κώδικα να να τον τρέξετε μέσα από ένα query στον SQL Server με επιλεγμένη βάση την master. Αυτός θα σας φτίαξει μια stored procedure σε αυτή την οποία μπορείτε να την εκτελέσετε ως εξής
EXEC dbo.spKillAllIdleSession <time>
<time> : είναι ο χρόνος σε λεπτά που το session δεν κάνει τίποτα δηλαδή αν πούμε 20 σημαίνει “σκότωσε όλα τα session που πάνω από 20 λεπτά δεν κάνουν τίποτα.
π.χ EXEC dbo.spKillAllIdleSession 20
CREATE PROC dbo.spKillAllIdleSessions
@IdleTime int -- Parameter to set your desirable idle time for session in minutes
/*
This stored procedure kills all idle processes in sql server 2005, 2008
except the current process and all processes on system databases
Created by Antonios Chatzipavlis Sep 27, 2009
*/
as
DECLARE @killCommand varchar(50)
DECLARE IdleSessions CURSOR FOR
SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
FROM master.sys.sysprocesses AS p
WHERE p.spid > 50
AND p.spid <> @@SPID
AND DateDiff(minute, p.last_batch, GetDate()) > @IdleTime
AND p.dbid not in (DB_ID('master'),DB_ID('model'),DB_ID('msdb'),DB_ID('tempdb'),
DB_ID('ReportServer'),DB_ID('ReportServerTempDB'),DB_ID('distribution'))
OPEN IdleSessions
FETCH IdleSessions INTO @killCommand
WHILE 0 = @@fetch_status
BEGIN
EXECUTE (@killCommand) -- Actually execute the command
FETCH IdleSessions INTO @killCommand
END
CLOSE IdleSessions
DEALLOCATE IdleSessions
GO
ΤΡΟΠΟΣ Β.
Αυτό ο τρόπος “σκοτώνει” όλα τα idle session σε συγκεκριμένη database εκτός από το session με το οποίο κάνουμε την εκτέλεση της εργασίας.
Για να το κάνετε αυτό πρέπει πρώτα να πάρετε τον παρακάτω κώδικα να να τον τρέξετε μέσα από ένα query στον SQL Server με επιλεγμένη βάση την master. Αυτός θα σας φτίαξει μια stored procedure σε αυτή την οποία μπορείτε να την εκτελέσετε ως εξής
EXEC dbo.spKillAllIdleSessionInDatabase <time>,<dbname>
<time> : είναι ο χρόνος σε λεπτά που το session δεν κάνει τίποτα δηλαδή αν πούμε 20 σημαίνει “σκότωσε όλα τα session που πάνω από 20 λεπτά δεν κάνουν τίποτα.
<dbname> : είναι το όνομα της database.
π.χ EXEC dbo.spKillAllIdleSessionInDatabase 20, ‘MyDB’
CREATE PROC dbo.spKillAllIdleSessionsInDatabase
@IdleTime int -- Parameter to set your desirable idle time for session in minutes
, @DBName varchar(50) -- Parameter to set database name
/*
This stored procedure kills all idle processes in a specific database
Created by Antonios Chatzipavlis Sep 27, 2009
*/
as
DECLARE @killCommand varchar(50)
DECLARE IdleSessions CURSOR FOR
SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
FROM master.sys.sysprocesses AS p
WHERE p.spid > 50
AND p.spid <> @@SPID
AND DateDiff(minute, p.last_batch, GetDate()) > @IdleTime
AND p.dbid =DB_ID(@DBName)
OPEN IdleSessions
FETCH IdleSessions INTO @killCommand
WHILE 0 = @@fetch_status
BEGIN
EXECUTE (@killCommand) -- Actually execute the command
FETCH IdleSessions INTO @killCommand
END
CLOSE IdleSessions
DEALLOCATE IdleSessions
GO
ΠΑΡΑΤΗΡΗΣΕΙΣ
- Τόσο κατά την δημιουργία, όσο και κατά την εκτέλεση ο χρήστης με τον οποίο έχετε κάνει login στον SQL Server θα πρέπει να είναι στον system role sysadmins.
- Εαν θέλετε να το φτιάξετε για SQL Server 2000 απλά αλλάξε το σημείο FROM master.sys.sysprocesses AS p σε FROM master.dbo.sysprocesses AS p