August 23, 2006 at 5:04 am
Is it possible to tell which spid process has been running for say 30000/ms and write the information to the application log..
I know oracle has time waited fields but not sure about sql server
Any input is welcome
Mike
August 24, 2006 at 1:38 pm
can you start with server - side traces?
August 24, 2006 at 1:54 pm
Thanks for your time
Not sure...How would server side traces track long running sql
August 24, 2006 at 2:05 pm
if exists (select top 1 spid from master..sysprocesses
where convert(bigint,datediff(n,last_batch,getdate()))*60 * 1000 > 30000 and spid > 50 and status = 'runnable')
print 'long process'
else
print 'no long process'
you can use xp_logevent to log the long process to the event log
August 24, 2006 at 2:23 pm
some more info...
from sql-server-performance.com
http://www.sql-server-performance.com/reducing_locks.asp
One way to help reduce locking issues is to identify those transactions that are taking a long time to run. The longer they take to run, the longer their locks will block other processes, causing contention and reduce performance. The following script can be run to identify current, long-running transactions. This will provide you with a clue as to what transactions are taking a long time, allowing you to investigate and resolve the cause.
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)
also this link is somewhat related too...
http://www.databasejournal.com/features/mssql/article.php/3500276
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply