December 6, 2007 at 10:29 am
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
December 6, 2007 at 11:26 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2007 at 10:38 am
I'd suggest you cycle your error log every day to avoid getting a really large log.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 10:43 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2007 at 1:56 pm
By the way "1204" is OK; "1205" is overkill !!!!
* Noel
December 10, 2007 at 4:28 pm
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 11, 2007 at 5:27 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2007 at 3:18 am
I tried 1222 and went back to 1204 + 1205.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 12, 2007 at 5:31 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply