go backsqlschool blogs list

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

by Antonios Chatzipavlis

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

ΠΑΡΑΤΗΡΗΣΕΙΣ
  1. Τόσο κατά την δημιουργία, όσο και κατά την εκτέλεση ο χρήστης με τον οποίο έχετε κάνει login στον SQL Server θα πρέπει να είναι στον system role sysadmins.
  2. Εαν θέλετε να το φτιάξετε για SQL Server 2000 απλά αλλάξε το σημείο FROM master.sys.sysprocesses AS p σε FROM master.dbo.sysprocesses AS p
    Ημερομηνία: 26 September 2009 14:43
    Αξιολόγηση:
    Κατηγορίες:
    Tags:
    Share it:

    Αφήστε το σχόλιο σας - Leave your comment

    Τα σχόλια έχουν κλείσει.
    Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


    newsletter subscription

    Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
    PASS chapter logo
    Official Professional Association for SQL Server (PASS) chapter for Greece
    Join to PASS