{"id":126,"date":"2015-08-18T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/deriks-favorite-trace-flags"},"modified":"2026-03-18T21:52:03","modified_gmt":"2026-03-18T21:52:03","slug":"deriks-favorite-trace-flags","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/08\/18\/deriks-favorite-trace-flags\/","title":{"rendered":"Derik&#8217;s Favorite Trace Flags &#8211; SQL Hammer"},"content":{"rendered":"<h2>Recommended defaults<\/h2>\n<h4>Tempdb<\/h4>\n<p><strong>Trace Flag 1118 \u2013 Allocate full extents only<\/strong><\/p>\n<p>Details:\u00a0<a href=\"http:\/\/www.brentozar.com\/archive\/2014\/06\/trace-flags-1117-1118-tempdb-configuration\/\">Trace Flags 1117, 1118, and Tempdb Configuration<\/a> | <a href=\"http:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\">Misconceptions around TF1118<\/a><\/p>\n<h4>Monitoring<\/h4>\n<p><strong>Trace Flag 3226 \u2013\u00a0Suppress successful backup messages in your error log<\/strong><\/p>\n<p>Details: <a href=\"http:\/\/www.sqlskills.com\/blogs\/paul\/fed-up-with-backup-success-messages-bloating-your-error-logs\/\">Fed up with BACKUP success messages bloating your error logs?<\/a><\/p>\n<h4>Performance<\/h4>\n<p><strong>Trace Flag 2371 \u2013\u00a0Enable dynamic threshold for\u00a0automatically updating statistics<\/strong><\/p>\n<p>Details: <a href=\"http:\/\/blogs.msdn.com\/b\/axinthefield\/archive\/2013\/10\/07\/sql-server-trace-flag-2371-for-dynamics-ax.aspx\">SQL Server Trace Flag 2371 for Dynamics AX<\/a><\/p>\n<p><strong>Trace Flag 4199 \u2013\u00a0Enable non-default Query Processor Enhancements<\/strong><\/p>\n<p>Details: <a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/974006\">SQL Server query optimizer hotfix trace flag 4199 servicing model<\/a><\/p>\n<p><strong>Trace Flag 2562 &#038; 2549 \u2013\u00a0Performance improvements for DBCC CHECKDB<\/strong><\/p>\n<p>Details: <a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/2634571\">Performance improvements for the DBCC CHECKDB command<\/a>\u00a0|\u00a0<a href=\"http:\/\/blogs.msdn.com\/b\/saponsqlserver\/archive\/2011\/12\/22\/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx\">Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 &#038; 2549<\/a><\/p>\n<h2>Useful<\/h2>\n<h4>Tempdb<\/h4>\n<p><strong>Trace Flag 1117 \u2013 Grow all files in a filegroup by their specified autogrow increment<\/strong><\/p>\n<p>This is categorized under \u201cUseful\u201d because it takes affect on the entire SQL Server instance. While this is extremely useful for tempdb, you need to be careful about whether you want your user databases to behave in this way.<\/p>\n<p>Details: <a href=\"http:\/\/blogs.technet.com\/technet_blog_images\/b\/sql_server_sizing_ha_and_performance_hints\/archive\/2012\/02\/09\/sql-server-2008-trace-flag-t-1117.aspx\">SQL Server 2008 -T 1117<\/a><\/p>\n<h4>Monitoring<\/h4>\n<p><strong>Trace Flag 1222 \u2013\u00a0Capture deadlock graphs in your SQL Server error log<\/strong><\/p>\n<p>This trace flag is still useful but I would first recommend you first investigate <a href=\"http:\/\/www.sqlservercentral.com\/articles\/deadlock\/65658\/\">Retrieving Deadlock Graphs with SQL Server Extended Events<\/a>.<\/p>\n<p>Details: <a href=\"http:\/\/www.sqlservercentral.com\/articles\/deadlocks\/74829\/\">Catching Deadlock Information in SQL Logs<\/a><\/p>\n<h4>Performance<\/h4>\n<p><strong>Trace Flag 2389 &#038; 2390\u00a0\u2013\u00a0Enable auto-quick-statistics update for known ascending keys and\/or all columns<\/strong><\/p>\n<p>Details: <a href=\"http:\/\/www.sqlservice.se\/sql-server-statistics-traceflags-2389-2390\/\">SQL Server statistics \u2013 traceflags 2389 &#038; 2390<\/a>\u00a0|\u00a0<a href=\"http:\/\/sqlmag.com\/t-sql\/making-most-automatic-statistics-updating\">Making the Most of Automatic Statistics Updating<\/a><\/p>\n<p><strong>Trace Flag 4139 \u2013\u00a0Enable auto-quick-statistics update for stationary columns<\/strong><\/p>\n<p>Details: <a href=\"https:\/\/milossql.wordpress.com\/2014\/12\/22\/beyond-statistics-histogram-part-3-tf-2389-feat-tf-4139\/\">Beyond Statistics Histogram \u2013 Part 3 (TF 2389 feat. TF 4139)<\/a><\/p>\n<p><strong>Trace Flag 1224 \u2013\u00a0Prevent lock escalation, except under extreme circumstances (increased concurrency)<\/strong><\/p>\n<p>Details: <a href=\"http:\/\/www.sqlservergeeks.com\/sql-server-trace-flag-1224\/\">SQL Server Trace Flag 1224<\/a><\/p>\n<p><strong>Trace Flag 3042 \u2013\u00a0Disable backup file pre-allocation algorithm to reduce disk space<\/strong><\/p>\n<p>Details: <a href=\"http:\/\/blogs.msdn.com\/b\/psssql\/archive\/2011\/08\/11\/how-compressed-is-your-backup.aspx\">How compressed is your backup?<\/a>\u00a0| <a href=\"http:\/\/www.msbicoe.com\/post\/2011\/10\/19\/The-Using-of-Trace-Flag-3042-for-Backup-Compression.aspx\">Using Trace Flag 3042 for Backup Compression<\/a><\/p>\n<h2>How?<\/h2>\n<p>Easily enable your favorite trace flags with this start-up procedure, borrowed from <a href=\"http:\/\/www.mssqlgirl.com\/important-trace-flag-that-every-expert-dba-should-know.html\">msSQLgirl<\/a>.<\/p>\n<pre><code class=\"language-\">USE [master]\nGO\n\n<p>CREATE PROC [dbo].[EnableTraceFlags]\n-- Author : Victor Isakov\n-- Company : SQL Server Solutions (http:\/\/www.sqlserversolutions.com.au)\n-- Purpose : Enable global trace flags upon SQL Server startup.\n-- Notes : Need to execute sp_procoption to enable this stored procedure to autoexecute\n-- whenever SQL Server instance starts:\n-- EXEC sp_procoption 'dbo.EnableTraceFlags', 'startup', 'true'\n-- Bugs : None\n-- Version : 1.0\n-- History :\n-- DATE DESCRIPTION\n-- ========== ==================================================\n-- 11\/04\/2011 Version 1.0 released.\nAS\nDBCC TRACEON (4199, -1);\n-- Enable Query Optimiser fixes (http:\/\/support.microsoft.com\/kb\/974006)\nDBCC TRACEON (1222, -1);\n-- Write deadlocks to errorlog (BOL)\nDBCC TRACEON (3226, -1);\n-- Supress successfull backup messages (BOL)\nGO<\/p>\n\n<p>EXEC sp_procoption N'[dbo].[EnableTraceFlags]', 'startup', '1'\nGO<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recommended defaults Tempdb Trace Flag 1118 \u2013 Allocate full extents only Details:\u00a0Trace Flags 1117, 1118, and Tempdb Configuration | Misconceptions around TF1118 Monitoring Trace Flag 3226 \u2013\u00a0Suppress successful backup messages in your error log Details: Fed up with BACKUP success messages bloating your error logs? Performance Trace Flag 2371 \u2013\u00a0Enable dynamic threshold for\u00a0automatically updating statistics [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-126","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=126"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/126\/revisions"}],"predecessor-version":[{"id":388,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/126\/revisions\/388"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}