trace flag 1222 produces wrong format (deadlock)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

trace flag 1222 produces wrong format (deadlock)

Author

Topic

MichaelHLutz
Starting Member

19 Posts

Posted - 2010-08-02 : 08:33:31

Hi, Microsoft documents the format for new deadlock trace flag 1222 here http://technet.microsoft.com/en-us/library/ms188396(SQL.90).aspx for SQL2005. I have read that this new flag only works on SP2 or greater but I find that both in SQL2008 and in SQL2005 SP3 the format for this trace flag is not as advertised. I'm not getting the new formatted output, but rather the old output styld from 1204. I've tried all combinations of 1222 on and 1204 off, and both 1222 and 1204 on.

Has anyone else experienced this? I've been searching for other posts on this issue but haven't found any.

I've tried about half a dozen environments and it seems hit or miss. Some create correct output and others don't.

Please help!!!
Thanks!
Mike

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-02 : 09:46:19

Post an example of the output you're getting.

Traceflag 1222 works from SQL 2005 RTM onwards. You don't need to be on SP2. Use DBCC TRACESTATUS() to see what traceflags are on. You just want 1222 on. 1204 should be off and there are no other traceflags that you need.

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-08-02 : 21:30:10

1222 is more readable and Concise compared to 1204. Tell us What was not Clear in the Content.

MichaelHLutz
Starting Member

19 Posts

Posted - 2010-08-24 : 20:39:08

Guys I apologize, now that I look more closely at the output it matches what Microsoft says will be there. Part of what threw me for a loop was that when viewing the log through SSMS it throws out the indentations. When I opened the log up in notepad it quickly matched what I was expecting to see.

Thanks for your help!

2010-08-24 20:30:14.69 spid52 DBCC TRACEON 1222, server process ID (SPID) 52. This is an informational message only; no user action is required.
2010-08-24 20:31:57.60 spid22s deadlock-list
2010-08-24 20:31:57.60 spid22s deadlock victim=process3833390
2010-08-24 20:31:57.60 spid22s process-list
2010-08-24 20:31:57.60 spid22s process id=process3833390 taskpriority=0 logused=328 waitresource=KEY: 2:504403158512107520 (020068e8b274) waittime=3415 ownerId=8540 transactionname=user_transaction lasttranstarted=2010-08-24T20:31:22.200 XDES=0x8f4e280 lockMode=S schedulerid=4 kpid=5752 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2010-08-24T20:31:54.190 lastbatchcompleted=2010-08-24T20:31:22.200 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHERYL-PC hostpid=5132 loginname=cheryl-PC\cheryl isolationlevel=read committed (2) xactid=8540 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2010-08-24 20:31:57.60 spid22s executionStack
2010-08-24 20:31:57.60 spid22s frame procname=adhoc line=2 stmtstart=4 sqlhandle=0x02000000b41aa010222e55c878a6cf23ac167fdb20b5a13b
2010-08-24 20:31:57.60 spid22s select * from blah
2010-08-24 20:31:57.60 spid22s inputbuf
2010-08-24 20:31:57.60 spid22s select * from blah
2010-08-24 20:31:57.60 spid22s process id=process3832e38 taskpriority=0 logused=328 waitresource=KEY: 2:504403158512107520 (010086470766) waittime=12422 ownerId=8593 transactionname=user_transaction lasttranstarted=2010-08-24T20:31:36.653 XDES=0x8f4fbe8 lockMode=S schedulerid=4 kpid=5524 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2010-08-24T20:31:45.183 lastbatchcompleted=2010-08-24T20:31:36.653 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHERYL-PC hostpid=5132 loginname=cheryl-PC\cheryl isolationlevel=read committed (2) xactid=8593 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2010-08-24 20:31:57.60 spid22s executionStack
2010-08-24 20:31:57.60 spid22s frame procname=adhoc line=2 stmtstart=4 sqlhandle=0x02000000b41aa010222e55c878a6cf23ac167fdb20b5a13b
2010-08-24 20:31:57.60 spid22s select * from blah
2010-08-24 20:31:57.60 spid22s inputbuf
2010-08-24 20:31:57.60 spid22s select * from blah
2010-08-24 20:31:57.60 spid22s resource-list
2010-08-24 20:31:57.60 spid22s keylock hobtid=504403158512107520 dbid=2 objectname=tempdb.dbo.blah indexname=PK__blah__A259EE5403317E3D id=lock5748040 mode=X associatedObjectId=504403158512107520
2010-08-24 20:31:57.60 spid22s owner-list
2010-08-24 20:31:57.60 spid22s owner id=process3832e38 mode=X
2010-08-24 20:31:57.60 spid22s waiter-list
2010-08-24 20:31:57.60 spid22s waiter id=process3833390 mode=S requestType=wait
2010-08-24 20:31:57.60 spid22s keylock hobtid=504403158512107520 dbid=2 objectname=tempdb.dbo.blah indexname=PK__blah__A259EE5403317E3D id=lock57476c0 mode=X associatedObjectId=504403158512107520
2010-08-24 20:31:57.60 spid22s owner-list
2010-08-24 20:31:57.60 spid22s owner id=process3833390 mode=X
2010-08-24 20:31:57.60 spid22s waiter-list
2010-08-24 20:31:57.60 spid22s waiter id=process3832e38 mode=S requestType=wait

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-25 : 01:43:42

SSMs not only throws out indentations, it shows the log entries in reverse order. I prefer a good text editor for reading the log any day.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources