{"id":1152,"date":"2007-10-12T17:09:43","date_gmt":"2007-10-12T17:09:43","guid":{"rendered":"\/blogs\/paul\/post\/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx"},"modified":"2017-08-04T12:10:26","modified_gmt":"2017-08-04T19:10:26","slug":"backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/","title":{"rendered":"BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it"},"content":{"rendered":"

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG<\/em> (or \u00a0TRUNCATE_ONLY<\/em> – they’re synonymous) to allow log truncation.<\/p>\n

How is it used?<\/strong><\/p>\n

The common use is when the transaction log grows to be inordinately large on a database that’s in FULL (or BULK_LOGGED) recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won’t truncate until its been backed up.\u00a0In these circumstances, if you don’t take a transaction log backup, the log will continue to grow until it runs out of disk space.<\/p>\n

The alternative to taking a real log backup\u00a0is to issue a BACKUP LOG dbname WITH NO_LOG<\/em> command. Let’s see this in action – note<\/strong> that I’m not advocating its use but I want to show you what it does. First off I’ll create some transaction log after taking a full database backup:<\/p>\n

\r\nUSE [nologtest];\r\nGO\r\n\r\nBACKUP DATABASE [nologtest] TO DISK = N'C:\\sqlskills\\nologtest.bck' WITH INIT;\r\nGO\r\n\r\nCREATE TABLE [t1] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a')\r\nGO\r\n\r\nSET NOCOUNT ON\r\nGO\r\n\r\nINSERT INTO [t1] DEFAULT VALUES;\r\nGO 10000\r\n<\/pre>\n
How large is the transaction log now?<\/p>\n
\r\nSELECT [name], [size] FROM sys.database_files;\r\nGO\r\n<\/pre>\n
\r\nname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 size\r\n--------------- --------\r\nnologtest\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 90264\r\nnologtest_log\u00a0\u00a0 104128\r\n<\/pre>\n
A little bit larger than the data file (which is what I’d expect after the operation I just performed) and they’re both around 100MB. I’ll pretend that I haven’t been paying attention to the size of the database and log and now I don’t have any space to perform a backup. Can I just shrink the log?<\/p>\n
\r\nDBCC SHRINKFILE (nologtest_log, 2);\r\nGO\r\n<\/pre>\n
\r\nCannot shrink log file 2 (nologtest_log) because all logical log files are in use.\r\nDbId\u00a0\u00a0 FileId\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentSize MinimumSize UsedPages\u00a0\u00a0 EstimatedPages\r\n------ ----------- ----------- ----------- ----------- --------------\r\n11\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 102944\u00a0\u00a0\u00a0\u00a0\u00a0 63\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 102944\u00a0\u00a0\u00a0\u00a0\u00a0 56\r\n<\/pre>\n
No. What’s stopping me (well DBCC SHRINKFILE<\/em> just told me, but let’s double-check)?<\/p>\n
\r\nSELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'nologtest';\r\nGO\r\n<\/pre>\n
\r\nLOG_BACKUP\r\n<\/pre>\n
Ok – so I can’t perform a backup so I’ll use BACKUP LOG WITH NO_LOG<\/em>:<\/p>\n
\r\nBACKUP LOG [nologtest] WITH NO_LOG;\r\nGO\r\n\r\nSELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'nologtest';\r\nGO\r\n<\/pre>\n
\r\nNOTHING\r\n<\/pre>\n
Now it looks like I can do the shrink:<\/p>\n
\r\nDBCC SHRINKFILE (nologtest_log, 2);\r\nGO\r\n<\/pre>\n
\r\nDbId\u00a0\u00a0 FileId\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentSize MinimumSize UsedPages\u00a0\u00a0 EstimatedPages\r\n------ ----------- ----------- ----------- ----------- --------------\r\n11\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 256\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 63\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 256\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 56\r\n<\/pre>\n
Success!<\/p>\n
Hold on, are you sure?<\/p>\n
Why is it bad?<\/strong><\/p>\n
Do you realize what just happened? We discarded<\/u><\/em> the contents of the transaction log since the last full\/differential\/log backup. That means that until the next full or differential backup completes<\/em>, all work since the last backup completed will be lost if the database is destroyed. So if your business relies on not losing data\/work in the event of a disaster – then what I just did is sacrilege<\/em>!<\/p>\n
The whole point<\/em> of FULL (and BULK_LOGGED) recovery modes\u00a0is to preserve the transaction log so that the database can be recovered right up to the point of a failure, or to do point-in-time restores. Using BACKUP LOG WITH NO_LOG<\/em> negates all of that. (For completeness, note that you can’t do a point-in-time restore to any time\u00a0in a transaction log backup containing a\u00a0bulk-logged transaction).<\/p>\n
What are the alternatives?<\/strong><\/p>\n
If you don’t want the FULL recovery mode behavior, then don’t use FULL recovery mode – switch to SIMPLE mode. But be aware that you lose the ability to restore to any point in between your full or differential backups.<\/p>\n
If you want the FULL recovery mode behavior, but don’t want to run out of log space – then the answer is again simple (pun intended :-), take log backups! If your log is growing too quickly, take more frequent log backups! If you don’t have enough disk space to store the log backups, talk to your management about the options – buying more disks and continuing to use the FULL recovery options, or being forced to move to SIMPLE recovery mode.<\/p>\n
One thing to bear in mind – you actually have to monitor the size of your log to tell whether its growing. That’s what gets people into trouble in the first place – a combination of:<\/p>\n
    \n
  • Not knowing that the database is in FULL recovery mode<\/li>\n
  • Not tracking the size of the log<\/li>\n<\/ul>\n
    The problem comes for those people who just use an app and have no idea that the developer put the database (maybe for a web application log) into FULL recovery mode, or even that they have SQL Server on their system. For these people there is no good answer unfortunately apart from educating developers.<\/p>\n
    How to prevent it being used<\/strong><\/p>\n
    If you’re a sysadmin who wants to stop your database owners and backup operators from using the NO_LOG<\/em> option, there is way to do it. Trace flag 3231 in SQL Server 2000 and SQL Server 2005 will turn the NO_LOG<\/em> and TRUNCATE_ONLY<\/em> options into no-ops in FULL\/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. Trace flag 3031 in SQL Server 2005 turns them in checkpoints in all recovery modes. These trace flags are undocumented but they’re allowed to be publicized.<\/p>\n
    This way no-one can truncate the log unless you allow them to, and when they come to you to complain, you can educate them!<\/p>\n","protected":false},"excerpt":{"rendered":"
    This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or \u00a0TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,14,31,35,38,96,98],"tags":[],"class_list":["post-1152","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-bad-advice","category-database-maintenance","category-disaster-recovery","category-example-scripts","category-trace-flags","category-transaction-log"],"yoast_head":"\nBACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal<\/title>\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\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or \u00a0TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-12T17:09:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-04T19:10:26+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\",\"name\":\"BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-12T17:09:43+00:00\",\"dateModified\":\"2017-08-04T19:10:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal","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\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/","og_locale":"en_US","og_type":"article","og_title":"BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal","og_description":"This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or \u00a0TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-12T17:09:43+00:00","article_modified_time":"2017-08-04T19:10:26+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/","name":"BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-12T17:09:43+00:00","dateModified":"2017-08-04T19:10:26+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1152"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1152\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}