Tuesday, January 6, 2015

Which trace flag do you need to capture deadlock evets ?

Enable Trace Flag 1204 and Trace Flag 1222
Find message  in below location:
G:\System Data and Logs\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

Find location from Startup Parameter
Sys Admin Permission required for below any activities
Enable Trace Flag
For current session level:
DBCC TRACEON(1222)
Go
DBCC TRACEON(1204)
Go
OR
DBCC TRACEON(1222, 1204)
Go
For global/instance level:
DBCC TRACEON(1222, -1)
Go
DBCC TRACEON(1204, -1)
Go
OR
DBCC TRACEON(1222,1204 -1)

Disable Trace:

DBCC TRACEOFF(1222)

DBCC TRACEOFF(1222, -1)

DBCC TRACEOFF(1222, 1204, -1)

Trace Flag on using Startup Parameter:

N.B No semicolon at end

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1222;-T1204

Verify Trace Status:
DBCC TRACESTATUS(-1)
GO
DBCC TRACESTATUS
GO

No comments:

Post a Comment