Logging Deadlocks In SQL Server
Article ID: KB0078090
Updated On:
| Products | Versions |
|---|---|
| TIBCO Cloud Integration - Connect ( Scribe ) | - |
Description
SQL Server Tools, such as Activity Monitor, are great for monitoring blocks or deadlocks in real-time. If real-time monitoring is not possible, you can to send that information to the SQL Server Error Log.
By Default SQL Server does not keep track of deadlocks. You need to run a script requesting that SQL Server keep track of deadlocks in SQL Server Error Log.
NOTE: If you restart the SQL Server service deadlock logging stops. Run the command again or create a SQL Agent job that runs this statement when the Agent starts.
SQL Statement To Enable Deadlock Tracing
By Default SQL Server does not keep track of deadlocks. You need to run a script requesting that SQL Server keep track of deadlocks in SQL Server Error Log.
Configuring The SQL Server Error Log
To trace all deadlock information and write it to the SQL Server Error Log with detailed information, run the following SQL statement:
NOTE: If you restart the SQL Server service deadlock logging stops. Run the command again or create a SQL Agent job that runs this statement when the Agent starts.
SQL Statement To Enable Deadlock Tracing
| /* -- What to Capture? -------------------- -- (1204) = Capture Deadlock Events. -- (1222) = Capture Deadlock Events with detailed Information (works only with SQL 2005 and higher version) -- Where to capture ? ------------------- -- (3605) = Capture the selected Events in the SQL Server error log. -- What is the Scope of Capture -------------------------------- -- (-1) = Switches on the specified trace flags globally, that means do it globally, for all users/sessions */ DBCC TRACEON (3605,1204,1222,-1) |
Viewing The SQL Server Error Log
Issue/Introduction
SQL Server Tools, such as Activity Monitor, are great for monitoring blocks or deadlocks in real-time.
Feedback
Yes
No
Powered by
