SQL Server – “Lock Timeouts” – Identifying through SQL Server Profiler

Preface

We experienced an outage this last Monday.

One of the errors logged in the WebSphere log file is the one pasted below:

Lock request time out period exceeded.

TroubleShooting

SQL Server Profiler

One of the most formidable tool for SQL Server TroubleShooting is SQL Server Profiler.

It allows the DBA to monitor several activities.

 

SQL Server Profiler – Take Care

Because it is an expensive to run it, I will suggest the following:

  1. Carefully choose which events you will like tracked
  2. Convert from Client Tracking to Server Tracing

 

SQL Server Profiler – Events

Here are the events to track on…

Tabulated

Events File Version
 Locks  
 Lock: Cancel
 Lock: Timeout
 Lock:Timeout ( timeout > 0)
 Stored Procedures
 RPC:Completed
 TSQL  
 SQL: BatchCompleted

 

Image

SQL Server Profiler – Filter

To augment the events we need to place filters and thus ensure we are not overloaded.

Tabulated

 Attribute Clause Qualifier
 Duration  
 Greater or equal to
 30000  ( Please note value is in milliseconds, and so we have it set for 30 seconds )
 Exclude rows that do not contain values

 

 

Image

 

 

Dedicated

Duckworth & Borris Callens

How to find timed out statements in SQL 2005 profiler
Link

 

References

  1. How to find timed out statements in SQL 2005 profiler
    Link
  2. Transcender
    • Application Deadlock
      You are the database administrator for a banking company. You manage all the SQL Server 2008 R2 databases of the company. The company stores customer-related data in the database named Cust01. This database is accessed by most users in the company for different purposes. The users daily perform insert and updates to the database through a .NET application.
      Eric, a user in the database, complains that his transaction has frozen and that he is not able to perform any operation in the database. You find out that the problem is due to a deadlock. You want to find out the user who is the other participant in the deadlock.
      Link

 

Summary

In follow-up posts, we will take things to the Lab and do things that will trigger timeouts.

3 thoughts on “SQL Server – “Lock Timeouts” – Identifying through SQL Server Profiler

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s