To Enable deadlock traces in global level in situations where we cannot predict when the deadlock will occur i have used the following methods after googling few sites.
We say “Trace flags” which denotes a logging key for a type of trace logging. Ex. Deadlock traces can be enabled by enabling flags “1204 and 1222″
To check the status of a flag, we use command as below,
DBCC TRACESTATUS (1204);
By running this, we will get an output similar to below,
Here, If Global has value “1” it means trace is enabled in global level (Not only for the current session). If Session has value “1” it means trace is enabled for the current session. “0” implies that the trace is not enabled for Session/Global level respectively.
To Turn On Tracing:
For enabling trace at the Session level we can use the following command,
DBCC TRACEON (1204)
DBCC TRACEON (1222)
To enable trace at the Global level which will be traced across sessions we can use the following command,
DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)
Viewing SQL Traces:
Once the log has been enabled in Global/Session level we can view the logs in any of the following methods,
1. Through Management Studio:
In SSMS, Object Explorer -> Management -> Sql Server Logs
2. Through Search Command:
Execute the following Command in SQL Query window, here “deadlock” is a search text. So that SQL Engine will result Error Log contents which has text “deadlock” in it.
exec xp_readerrorlog 0,1,’deadlock’
3. Through Log files repository:
To find log files repository OR to view all the logs in SQL window, execute the following command
This command will return all the log statements in Query Results window. To get folder path where the logs are written stop the execution when you get around 20 row(s). In the result window we will see a line similar to “Logging SQL Server messages in file ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.” This is the folder path on which the logs are being written.
To Turn Off Tracing:
As how we enabled Trace the same way we should Turn Off tracing. But the difference is, Instead of TRACEON statement we have to use TRACEOFF statement. If we have enabled Session level tracing it should be turned off as below,
DBCC TRACEOFF (1204);
If we have enabled in Global Level, it should be as follows
DBCC TRACEOFF (1204,-1);
Simulating a Deadlock to verify Tracing:
Run the following statement once,
IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
CREATE TABLE dbo.codl(id int identity(1,1) primary key clustered, col1 char(10) default ‘abc’)
INSERT INTO dbo.codl DEFAULT VALUES
Then, run the following Query statement in two different SQL Management Studio Sessions simultaneously. This will raise a deadlock in any one of the sessions.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM dbo.codl WHERE ID = 1
WAITFOR DELAY ’00:00:05′
SET col1 = ‘xyz’ WHERE id = 1
Now using Trace which we have enabled, we can get details of deadlock occurrences.