lock timeout

Hello All-
I've been troubleshooting deadlocks in a third-party app. I ran a trace with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and all Error and Warning events. I then imported the output files into a table for analysis. My results show 90,000+ Lock:Timeout events and 20 Lock:Deadlock events for a 5 hour period! The vast majority of the Lock:Timeout events are for index or table resources. The server is running SQL2K, sp3a.
Here's my questions:
-Shouldn't I be seeing corresponding Attention or Exception events for the Lock:Timeouts? I thought SQL Server returned an error 1229 to the client and terminated the statement on a lock timeout. Would I not see this in the trace? Do I really have 90,000+ statements being terminated?
-a small number of the database ids in the Lock:Timeout events are large ids not included in sysdatabases. Where are these coming from? A bug?
-My developers swear that they are not setting a lock_timeout on their connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace output. So why am I getting all these timeouts? I thought SQL Server by default waited indefinitely on a lock.
I confused! Any insight would be greatly appreciated.
thanks-
MargaretUnfortunately that counter is pretty much useless since it includes internal
lightweight timeouts that are normal and always present. You pretty much
have to forget about the majority of them as normal.
--
Andrew J. Kelly SQL MVP
"Margaret" <anonymous@.discussions.microsoft.com> wrote in message
news:57AEA811-A059-4426-B023-5FD611C7D330@.microsoft.com...
> Hello All-
> I've been troubleshooting deadlocks in a third-party app. I ran a trace
with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and
all Error and Warning events. I then imported the output files into a table
for analysis. My results show 90,000+ Lock:Timeout events and 20
Lock:Deadlock events for a 5 hour period! The vast majority of the
Lock:Timeout events are for index or table resources. The server is running
SQL2K, sp3a.
> Here's my questions:
> -Shouldn't I be seeing corresponding Attention or Exception events for the
Lock:Timeouts? I thought SQL Server returned an error 1229 to the client
and terminated the statement on a lock timeout. Would I not see this in the
trace? Do I really have 90,000+ statements being terminated?
> -a small number of the database ids in the Lock:Timeout events are large
ids not included in sysdatabases. Where are these coming from? A bug?
> -My developers swear that they are not setting a lock_timeout on their
connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace
output. So why am I getting all these timeouts? I thought SQL Server by
default waited indefinitely on a lock.
> I confused! Any insight would be greatly appreciated.
> thanks-
> Margaret
>