Deadlock file location

Question

Deadlock file location

PRAMANA.DBA

SSCertifiable

Points: 5507
More actions
September 9, 2014 at 6:08 am

#293013

Hi All,
I have enabled 1222 and 1204 trace and restarted sql server. then i have replicated deadlock in my local.
This is the error came in sql
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Then i opened ERRORLOG file , there is no information about deadlocks .
Where can found deadlock information and file path. is there any extra options required to enable trace?
DBCC TRACESTATUS(1222,-1)
TraceFlagStatusGlobalSession
1222110
can you please help me , how to find location trace log?
September 10, 2014 at 12:30 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

With trace flag 1222 running the deadlock information will be recorded in the SQL Server log.

Open up SSMS, then got to Management > SQL Server Log. Then open the log that corresponds with the time the deadlock occurred.

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 2

DBA From The Cold (9/9/2014)
With trace flag 1222 running the deadlock information will be recorded in the SQL Server log.
Open up SSMS, then got to Management > SQL Server Log. Then open the log that corresponds with the time the deadlock occurred.

below path:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\errorlog

but there is no information about deadlock

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 4

Did you restart your local instance after the deadlock occurred?

If you did, the deadlock information would be contained in one of the other error log files:-

ERRORLOG.1

ERRORLOG.2

etc

Have you looked in those files?

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 5

I did below steps,

dbcc traceon(1204)

DBCC TRACEstatus (1204)

TraceFlagStatusGlobalSession

1204101

restarted SQL then i replicated deadlock , in log not showing any thing about deadlock , after that i have verified status trace

DBCC TRACEstatus (1204)

TraceFlagStatusGlobalSession

1204000

observation: After re starting sql , 1204 status got changed.

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 6

DBA From The Cold (9/9/2014)
Did you restart your local instance after the deadlock occurred?
If you did, the deadlock information would be contained in one of the other error log files:-
ERRORLOG.1
ERRORLOG.2
etc
Have you looked in those files?

yes, i have verified old logs also.

Sean Pearce SSCoach Points: 15999 More actions · Answer 7

PRAMANA.DBA (9/9/2014)
I did below steps,
dbcc traceon(1204)
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204101
restarted SQL then i replicated deadlock , in log not showing any thing about deadlock , after that i have verified status trace
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204000
observation: After re starting sql , 1204 status got changed.

The trace flag doesn't persist when you restart SQL. You need to TRACEON after the restart.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Don't use traceflag 1204 on SQL Server 2005 or above. If you want to use a deadlock traceflag, use 1222. But in SQL 2012 you don't need to use either as deadlocks are automatically caught in the system health extended events session.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 9

Sean Pearce (9/10/2014)
PRAMANA.DBA (9/9/2014)
I did below steps,
dbcc traceon(1204)
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204101
restarted SQL then i replicated deadlock , in log not showing any thing about deadlock , after that i have verified status trace
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204000
observation: After re starting sql , 1204 status got changed.
The trace flag doesn't persist when you restart SQL. You need to TRACEON after the restart.

Thanks Sean Pearce. Is there any option to set to always trace on . with out depends on restart.

Sean Pearce SSCoach Points: 15999 More actions · Answer 10

PRAMANA.DBA (9/10/2014)
Sean Pearce (9/10/2014)
PRAMANA.DBA (9/9/2014)
I did below steps,
dbcc traceon(1204)
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204101
restarted SQL then i replicated deadlock , in log not showing any thing about deadlock , after that i have verified status trace
DBCC TRACEstatus (1204)
TraceFlagStatusGlobalSession
1204000
observation: After re starting sql , 1204 status got changed.
The trace flag doesn't persist when you restart SQL. You need to TRACEON after the restart.
Thanks Sean Pearce. Is there any option to set to always trace on . with out depends on restart.

You can use command line parameters when starting SQL.

http://msdn.microsoft.com/en-us/library/ms190737.aspx

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]