case for 'DBCC TRACEON - Trace Flags'

Question

Post reply

case for 'DBCC TRACEON - Trace Flags'

VAI

SSC Enthusiast

Points: 185
More actions
March 18, 2024 at 10:31 pm

#4397733

Please share a situation when enabling and taking further advantage of DBCC TRACEON - Trace Flags
made a serious difference for you, solved a problem, etc. - in other words, in what situation did you ENABLE a certain Trace Flag and it helped you tremendously? (in what, how..)
THANKS!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 734469 More actions · Answer 2

There are a few scenarios where this has been helpful. I've had clients that used 101/102 for merge loggin to track down issues with replication.

There are some that people will set periodically. During large delete periods, there are people who have set 661 to stop the ghost cleanup process. This can sometimes consume resources you need for a busy workload, so disabling this allows you to defer this operation to less busy times.

I know I've used a few others, but I can't give a situation right now as none kick off memories. I know some people have worred about 1118, with page allocation contention. The lock escalation flags I know can help with overloaded servers. I haven't used any of the optimizer ones, but I have heard people saying they help for some workloads. Some of these can be used by the query or session level and I've seen code with these. I assume this was well tested, but I don't know.

Is there a reason for the question? In general, these are feature flags that you should test in a situation to determine if it is helpful or not.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 3

See the following for the list of supported Trace Flags.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

1118 (No longer required but I used to have it on server wide. I have the equivalent turned on in every database.)

692 (Super important in mixed or strict OLTP environments. I always turn it on server wide. Go read about it. It almost always makes a mess if you don't and it gets covered up if you make the mistake of doing regular index maintenance.)

3326 (Turn offs messages in the error log about successful backups, which can take a huge amount of space if you're doing things right.)

I also download at least the latest version of the "ACE" driver/providers so that I don't need to go anywhere near SSIS just to import a spreadsheet.

https://www.microsoft.com/en-us/download/details.aspx?id=54920&irgwc=1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)