May 21, 2009 at 2:25 pm
Can I turn trace flags ON on my server forever? Does it affects database and query performance a lot?
May 21, 2009 at 2:35 pm
dallas13 (5/21/2009)
Can I turn trace flags ON on my server forever? Does it affects database and query performance a lot?
What kind of Trace flags R u planning to turn on?
May 21, 2009 at 2:39 pm
dbcc traceon(1204,3605,-1)
dbcc traceon(1205,-1)
dbcc traceon(3604,-1)
All of above...
May 21, 2009 at 2:44 pm
1204 is o.k, 1205 logs more detailed information. I have had those trace flags on for about a month. I havent seen any performance degradation due to those trace flags but I would definetly suggest you to keep an eye on your Log file, at least once in 10 days
May 21, 2009 at 2:56 pm
Why do u need to Turn on both 3604 and 3605? Both do the same thing 3604 send information to Client (Query analyzer) and the other to Log file. I would just enable 3605 and keep eye on my log file and then plan accordingly about when to flush my log file on some periodic basis
May 22, 2009 at 3:40 am
dallas13 (5/21/2009)
dbcc traceon(1204,3605,-1)dbcc traceon(1205,-1)
dbcc traceon(3604,-1)
All of above...
You don't need all of those. If you're looking for deadlocks, the only one that you need is 1204.
1205 adds a lot, lot more detail on what lead up to the deadlock. Not usually necessary, lots of info to wade through.
3604 and 3605 direct some outputs to the screen or error log respectively. They are not necessary with the deadlock traceflag as the deadlock traceflags send output to the error log regardless of the setting of 3605.
dbcc traceon(1204,-1)
And bear in mind that it only active until you restart SQL.
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
May 22, 2009 at 4:14 am
May 22, 2009 at 9:53 am
Thanks all for yr replies...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply