Πώς να “σκοτώσω” τα ανενεργά connections στον SQL Server

Entry posted by antonch · September 26, 2009

1183 views

Αφορμή για αυτό το 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

: είναι ο χρόνος σε λεπτά που το 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 με το οποίο κάνουμε την εκτέλεση της εργασίας.

EXEC dbo.spKillAllIdleSessionInDatabase ,

: είναι το όνομα της 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