Reducing SQL Server Locks

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)

This query provides results based on the instant is runs, and will vary each time you run it. The goal is to review the results and look for transactions that have been open a long time. This generally indicates some problem that should be investigated. [7.0, 2000] Updated 10-16-2005

*****

In order to reduce blocking locks in an application, you must first identify them. Once they are identified, you can then evaluate what is going on, and perhaps be able to take the necessary action to prevent them. The following script can be run to identify processes that have blocking locks that occur longer than a time you specify. You can set the value used to identify blocking locks to any value you want. In the example below, it is for 10 seconds.

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000      --The wait time is measured in milliseconds
AND spid > 50      -- Use > 50 for SQL Server 2000, use > 12 for SQL Server 7.0

This query measures blocking locks in real-time, which means that only if there is a blocking lock fitting your time criteria when you run this query will you get any results. If you like, you can add some additional code that will loop through the above code periodically in order to more easily identify locking issues. Or, you can just run the above code during times when you think that locking is a problem. [7.0, 2000] Updated 10-16-2005