Trace Deadlocks with SQL Server Profiler

Deadlocks can happen at anytime and processes compete for locks, threads, memory or other shareable resources. SQL server periodically detected deadlocks and kill one of the blocking one as victim. In many cases, we want to know the processes involved in order to eliminate or reduce the deadlock conditions.

Here is a step by step instruction on how to capture the deadlocks with Microsoft SQL Server Management Studio.

  • Go to Tools -> SQL Server Profiler
  • Enter your credentials to connect to the database on the popup
  • From the Trace Properties->General tabe, enter the Trace Name, Use the template (drop down and select Blank), Save to File (or table)
  • Trace_property_general

  • Go to the Events Selection tab, expand Locks, select Deadlock graph
  • Trace_property_events

  • Config the extract settings on the Events Extraction Settings tab
  • Trace_property_extraction_settings

  • Click Run button
  • Once you have deadlocks captured, you can open and view the graphs by File->Open from SQL Profiler.

    Advertisements
    This entry was posted in Database, Tool and Debug and tagged , , . Bookmark the permalink.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    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