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:
Post a Comment