Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Trace Flags to turn on for SQL instances < 2012 Expand / Collapse
Posted Friday, January 18, 2013 12:51 PM


Group: General Forum Members
Last Login: Thursday, September 1, 2016 7:38 AM
Points: 102, Visits: 766
I need some opinions here.

There are 2 trace flags
3504 - For internal testing. Will raise a bogus log-out-of-space condition from checkpoint()


1118 - Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent

In this blog post, at the very bottom, Paul says 1118 applies to not just tempdb, but all user databases. In a 2012 edit of that post, he recommends turning it on for all SQL instance >= 2000 and < 2012.

3504 is another trace flag that is turned on by default in 2012(thank you Ben Miller for confirming this).
See Ben's recent experience with 2012 in this podcast.

Ben found some IO subsystem problems with 2012 that would not have showed up in the ErrorLog if he had used 2008 R2. I recommend listening to this podcast.

So, I built two 2008 R2 clusters for a Sharepoint 2010 farm. I'm worried about performance, contention in TempDB and I also want to get more Z's knowing the IO subsystem my SAN friends gave me is working well.

Should I enable these two trace flags on both clusters, one is a Content database cluster, the other is a Search database cluster, shared SAN drives?

Using the following information, I would say yes. Any thoughts?
TF 1118
see [Edit: 2012]

TF 3504
(thank you Ben)

Post #1409069
Posted Tuesday, January 22, 2013 5:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 279, Visits: 1,209
I'll only enable TF 1118...only if I have TempDB contention and even if splitting the files doesn't help. However, as Sql Server Best Practice Analyzer suggests you should add this trace flag as one of the start up parameters.

Post #1409979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse