Wednesday, August 5, 2009

Connection leak

A quick script to check connection leaks.


SELECT   spid,
         loginame,
         hostname,
         program_name,
         cpu,
         physical_io,
         memusage,
         open_tran,
         Db_name
(dbid)
FROM     MASTER..sysprocesses
WHERE    spid > 50
         AND Datediff
(hh,last_batch,Getdate()) > 4
ORDER BY last_batch


This script can be handy in checking for unclosed connections by any program on the database server. Last_batch column on sysprocesses indicates the time at which the last query was executed by a connection.Spids below 50 refer to system connections and hence they have been filtered.This query lists down the processes that have been holding on to the connection for more than 4 hours.Most of such connections have their current status to be waiting.


SELECT   spid,
         
query_name.TEXT,

         
loginame,

         
hostname,

         
program_name,

         
cpu,

         
physical_io,

         
memusage,

         open_tran,

         
Db_name(MASTER..sysprocesses.dbid)

FROM     MASTER..sysprocesses

         
CROSS APPLY Fn_get_sql
(sql_handle) query_name
WHERE    spid >
 50
         
AND Datediff
(hh,last_batch,Getdate()) > 4
ORDER BY last_batch


If you are on sql 2005 use the above query to get the last query executed as well.

No comments: