Trace Flags 1204 and 1205 :crazy:

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Trace Flags 1204 and 1205 :crazy:

Trace Flags 1204 and 1205 :crazy:

Rate Topic

Display Mode

Topic Options

Author

Message

Frank Lain

Posted Thursday, December 06, 2007 10:29 AM

Valued Member

Group: General Forum Members
Last Login: Friday, December 09, 2011 10:51 AM
Points: 51, Visits: 35

I have a client who is getting numerous Deadlock errors:

Transaction (Process ID 102) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When I run Profiler for Deadlock and Deadlock Chain events, it does not return any useful information, like what SQL statements were involved with the Deadlock. While researching on the net, I came across many references to Trace Flags.

My question is, when I run DBCC TRACEON(1204,1205), where do I see the results? Do I use Profiler?
Also, what kind of performance impact will running these trace flags have? Is it bad to leave these trace flags on for extended periods (multiple hours)?

Any help would be greatly appreciated!

--Frank

Post #430315

Grant Fritchey

Posted Thursday, December 06, 2007 11:26 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 11,885, Visits: 22,843

The output will be to the error log. Get ready, it's messy.

I've never seen a performance hit from those flags or heard about one. We set ours on in several of our environments and leave them on all the time.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #430353

colin.Leversuch-Roberts

Posted Monday, December 10, 2007 10:38 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:50 AM
Points: 2,641, Visits: 669

I'd suggest you cycle your error log every day to avoid getting a really large log.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #431422

Grant Fritchey

Posted Monday, December 10, 2007 10:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 11,885, Visits: 22,843

If you're not already cycling your error log, you should.

Daily seems a bit high. We cycle ours once a week.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #431431

noeld

Posted Monday, December 10, 2007 1:56 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 6,259, Visits: 1,911

By the way "1204" is OK; "1205" is overkill !!!!

* Noel

Post #431523

colin.Leversuch-Roberts

Posted Monday, December 10, 2007 4:28 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:50 AM
Points: 2,641, Visits: 669

it's a matter of scale, I usually get 40 - 80 mb per day running the trace flags, it's fine as long as you don't want to read the logs in SSMS - I usually use wordpad - I don't agree about 1205, I find both are best for diagnostic work.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #431577

Grant Fritchey

Posted Tuesday, December 11, 2007 5:27 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 11,885, Visits: 22,843

We use just 1204. I'm wondering though if we shouldn't switch it over to 1222. The XML output looks easier to read. Anyone using that?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #431714

colin.Leversuch-Roberts

Posted Wednesday, December 12, 2007 3:18 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:50 AM
Points: 2,641, Visits: 669

I tried 1222 and went back to 1204 + 1205.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #432204

Grant Fritchey

Posted Wednesday, December 12, 2007 5:31 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 11,885, Visits: 22,843

That's certainly not a ringing endorsement. Anything in particular force the change back?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #432242

« Prev Topic | Next Topic »

Permissions