{"id":815,"date":"2017-03-02T06:00:25","date_gmt":"2017-03-02T14:00:25","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=815"},"modified":"2017-04-13T09:19:55","modified_gmt":"2017-04-13T16:19:55","slug":"sqlskills-101-trace-flags","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/","title":{"rendered":"SQLskills SQL101: Trace Flags"},"content":{"rendered":"<blockquote><p>\u201cYou should always use trace flag X for a SQL Server install.\u201d<\/p>\n<p>\u201cHave you tried trace flag Y?\u201d<\/p>\n<p>\u201cWe fixed the problem using an undocumented trace flag.\u201d<\/p><\/blockquote>\n<p>If you\u2019re new to SQL Server, you might have heard or read some of the above statements.\u00a0 If you\u2019ve never used a trace flag, you might wonder why you might need one, and how you would know if you did need it.\u00a0 SQL Server trace flags are used to change the behavior of the engine in some way.\u00a0 A trace flag is ideally used for improvement, but there can be situations where a trace flag doesn\u2019t provide the intended benefit.\u00a0 In some cases, it can adversely affect the problem you\u2019re trying to fix, or create a different issue.\u00a0 As such, trace flags in SQL Server are something to use with caution.\u00a0 The number one recommendation I always make when someone asks about using a trace flag is to test it, ideally in an identical or comparable situation.\u00a0 This isn\u2019t always possible, which is why there\u2019s always a slight risk with trace flags.\u00a0 There are only three (3) trace flags that we at SQLskills recommend, by default, for a SQL Server installation:<\/p>\n<ul>\n<li>1118 (for versions prior to SQL Server 2016)<\/li>\n<li>3023 (for versions prior to SQL Server 2014)<\/li>\n<li>3226<\/li>\n<\/ul>\n<p>Trace flag 1118 addresses contention that can exist on a particular type of page in a database, the SGAM page.\u00a0 This trace flag typically provides benefit for customers that make heavy use of the tempdb system database.\u00a0 In SQL Server 2016, you change this behavior using the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-set-options\">MIXED_PAGE_ALLOCATION<\/a> database option, and there is no need for TF 1118.<\/p>\n<p>Trace flag 3023 is used to enable the CHECKSUM option, by default, for all backups taken on an instance.\u00a0 With this option enabled, page checksums are validated during a backup, and a checksum for the entire backup is generated. \u00a0Starting in SQL Server 2014, this option can be set instance-wide through sp_configure (\u2018<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/mt665414(v=sql.120).aspx\" class=\"broken_link\">backup checksum default<\/a>\u2019).<\/p>\n<p>The last trace flag, 3226, prevents the writing of successful backup messages to the SQL Server ERRORLOG.\u00a0 Information about successful backups is still written to msdb and can be queried using T-SQL.\u00a0 For servers with multiple databases and regular transaction log backups, enabling this option means the ERRORLOG is no longer bloated with <em>BACKUP DATABASE<\/em> and <em>Database backed up<\/em> messages.\u00a0 As a DBA, this is a good thing because when I look in my ERRORLOG, I really only want to see errors, I don\u2019t want to scroll through hundreds or thousands of entries about successful backups.<\/p>\n<p>You can find a <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql\">list of supported trace flags on MSDN<\/a>, and as I alluded to initially, there are undocumented trace flags. \u00a0An undocumented trace flag is one that is not supported by Microsoft.\u00a0 If you ever use an undocumented trace flag and you have a problem, Microsoft will not provide support for that problem; if you decide to use an undocumented trace flag, tread carefully, particularly in production.<\/p>\n<p>How will you know if you should use a trace flag?\u00a0 Online you\u2019ll typically come across a forum post, blog post, or article that describes a scenario that you might be having, with the recommendation that you fix it with a trace flag.\u00a0 You could also attend a user group meeting, a SQLSaturday or conference session, and hear the same thing.\u00a0 You may have it recommended to you by a consultant, or another DBA or developer.\u00a0 In all cases, it\u2019s important to first confirm that what you\u2019re seeing in your environment matches the behavior described by the trace flag.\u00a0 If you believe you should enable a trace flag, enable it in a test or development environment first where you can recreate the problem, and then test it thoroughly.\u00a0 Finally, after it\u2019s gone through rigorous testing, you can try it in production.\u00a0 Notice I say \u201ctry\u201d because even with all your testing, if may not be the right solution for your environment.<\/p>\n<p>If you find you do want to give a trace flag try, there are two ways to enable\/disable them:<\/p>\n<ul>\n<li>Using <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-transact-sql\">DBCC TRACEON<\/a> and <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceoff-transact-sql\">DBCC TRACEOFF<\/a><\/li>\n<li>Using \u2013T with the trace flag as a startup parameter for the SQL Server service<\/li>\n<\/ul>\n<p>Enabling a trace flag with DBCC TRACEON is done using T-SQL, and you have the option to set the trace flag at the session or global level.\u00a0 Typically you want the trace flag to be used by the entire instance, so you enable it globally.\u00a0 For testing purposes, you may just enable it at the session level.\u00a0 To enable trace flag 3226 globally you would run:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC TRACEON (3226, -1);\r\nGO\r\n<\/pre>\n<p>The use of -1 turns on the flag for the entire instance.\u00a0 To disable the trace flag you run:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC TRACEOFF (3226, -1);\r\nGO\r\n<\/pre>\n<p>The advantage of using DBCC TRACEON and DBCC TRACEOFF is that you don\u2019t have to restart the instance to use the trace flag.\u00a0 The drawback is that it can be disabled by anyone who has sysadmin membership and runs DBCC TRACEOFF, and that it will not persist through a restart.\u00a0 I recommend using this option when testing a trace flag.<\/p>\n<p>For cases where you\u2019ve tested the trace flag and you know that you want it enabled, then you want to add it to the SQL Server service as a startup parameter.\u00a0 This requires using SQL Server Configuration Manager.\u00a0 Once you have Configuration Manager open, select Services on the left side and then you\u2019ll see all the services listed on the right.\u00a0 Highlight the SQL Server service, right-click and select Properties, then select the Startup Parameters tab.\u00a0 To add a startup parameter use the syntax \u2013T followed by the trace flag, as shown below:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"size-large wp-image-816\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-1024x663.jpg\" alt=\"Adding TF 3226 as a startup parameter for the SQL Server service\" width=\"1024\" height=\"663\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-1024x663.jpg 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-300x194.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-900x583.jpg 900w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf.jpg 1160w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Adding TF 3226 as a startup parameter for the SQL Server service<\/p>\n<p>Note: There should be no space between the \u2013T and the trace flag (but if you try and put one there, SQL Server removes it for you).<\/p>\n<p>Then select Add so it appears in the Existing parameters: window, and then OK, and you will be notified that the change will not take effect until you restart the instance.\u00a0 If you are not able to restart the instance immediately, you can apply it using DBCC TRACEON, just be aware that someone could remove it.<\/p>\n<p>Lastly, to check what trace flags, if any, are enabled for your instance, you can use DBCC TRACESTATUS.\u00a0 In our case, the output shows that we have 3226 enabled globally:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC TRACESTATUS;\r\nGO\r\n<\/pre>\n<figure id=\"attachment_817\" aria-describedby=\"caption-attachment-817\" style=\"width: 393px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/trf2.jpg\"><img decoding=\"async\" class=\"size-full wp-image-817\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/trf2.jpg\" alt=\"DBCC TRACESTATUS output showing TF 3226 enabled\" width=\"393\" height=\"78\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/trf2.jpg 393w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/trf2-300x60.jpg 300w\" sizes=\"(max-width: 393px) 100vw, 393px\" \/><\/a><figcaption id=\"caption-attachment-817\" class=\"wp-caption-text\">DBCC TRACESTATUS output showing TF 3226 enabled<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>As you can see, using trace flags is pretty straight-forward.\u00a0 However, deciding whether a trace flag is needed and then testing to ensure it provides benefit and not detriment is what requires real work.\u00a0 Use trace flags wisely, and always test first!\u00a0 And remember, if you want to find all of our <strong>SQLskills SQL101<\/strong> blog posts visit <a href=\"https:\/\/www.SQLskills.com\/help\/sql101\">SQLskills.com\/help\/SQL101.<\/a><\/p>\n<p>Additional reading:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\">Misconceptions around TF 1118<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/backup-checksum-default-option-in-sql-server-2014\/\">Backup Checksum Default Option in SQL Server 2014<\/a><\/li>\n<li><a href=\"https:\/\/blogs.msdn.microsoft.com\/sqlserverstorageengine\/2007\/10\/30\/when-is-too-much-success-a-bad-thing\/\">When is too much success a bad thing?<\/a><\/li>\n<li><a href=\"https:\/\/www.pluralsight.com\/courses\/sql-server-understanding-using-dbcc-commands\">SQL Server: Understanding and Using DBCC Commands<\/a> (Pluralsight course)<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u201cYou should always use trace flag X for a SQL Server install.\u201d \u201cHave you tried trace flag Y?\u201d \u201cWe fixed the problem using an undocumented trace flag.\u201d If you\u2019re new to SQL Server, you might have heard or read some of the above statements.\u00a0 If you\u2019ve never used a trace flag, you might wonder why [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,47],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Trace Flags - Erin Stellato<\/title>\n<meta name=\"description\" content=\"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what&#039;s enabled.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Trace Flags - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what&#039;s enabled.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-02T14:00:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-1024x663.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/\",\"name\":\"SQLskills SQL101: Trace Flags - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-03-02T14:00:25+00:00\",\"dateModified\":\"2017-04-13T16:19:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what's enabled.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Trace Flags\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLskills SQL101: Trace Flags - Erin Stellato","description":"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what's enabled.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Trace Flags - Erin Stellato","og_description":"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what's enabled.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/","og_site_name":"Erin Stellato","article_published_time":"2017-03-02T14:00:25+00:00","article_modified_time":"2017-04-13T16:19:55+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/03\/tf-1024x663.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/","name":"SQLskills SQL101: Trace Flags - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-03-02T14:00:25+00:00","dateModified":"2017-04-13T16:19:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"In this post we review what a trace flag is in SQL Server and how to know if you should use one. We also cover how to enable them and see what's enabled.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-trace-flags\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Trace Flags"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/815"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=815"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/815\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}