Thursday, June 29, 2006

connection not closed or Long running Queries

The following code will help to find the spid's that are sitting on your server for long time. This could happen when a connection is not closed or when the queries you are running is taking long time.

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]FROM master..sysprocesses pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 1000ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)

No comments: