articles

Articles of SQLschool.gr Team

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

Antonios Chatzipavlis

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

EXEC dbo.spKillAllIdleSessionInDatabase <time>,<dbname>

<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

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.