How to get deadlocks in SQL Server 2008?
I am using SQL SERVER 2008 R2 for my development activities. You can use a deadlock graph and gather the information you require from the log file. The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table.
Where to find deadlock graph in SQL Server?
System_health session does capture deadlock_graph by default. This extended event session is created by default and would be running on the server. So, you can grab system_health*.xel files located under LOG folder (where ERRORLOGs are located) and run query at them to see if any deadlock has happened.
What happens if there is a deadlock in a session?
When you start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event. The task with the blue X on it is the victim.
What does deadlock ID 103 mean in SQL Server?
Users might complain about slowness or error messages. And, you’ll see a message in the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.” Your server has encountered a deadlock.
How to find deadlock graphs in SQL Server?
Prior to SQL Server 2008, there was no way to retroactively find deadlock information. Obtaining deadlock graphs required that a SQL Trace was actively running, or that Trace Flag 1222 or 1205 were turned on for the instance.
How can I get deadlock graphs without a profiler?
In order to capture deadlock graphs without using a trace (you don’t need profiler necessarily), you can enable trace flag 1222. This will write deadlock information to the error log. However, the error log is textual, so you won’t get nice deadlock graph pictures – you’ll have to read the text of the deadlocks to figure it out.
What is the default trace in SQL Server?
SQL Server 2005 added the default trace as a sort of “black box” for a SQL Server. SQL Server 2008 expands upon this with the system_health default session in Extended Events.