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.