{"id":717,"date":"2010-04-14T06:07:00","date_gmt":"2010-04-14T06:07:00","guid":{"rendered":"\/blogs\/paul\/post\/A-DBA-myth-a-day-(1430)-clearing-the-log-zeroes-out-log-records.aspx"},"modified":"2017-07-12T12:50:15","modified_gmt":"2017-07-12T19:50:15","slug":"a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/","title":{"rendered":"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records"},"content":{"rendered":"

(Look in the Misconceptions<\/a> blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list<\/a>, plus my online Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n

Today is the big day – 8am PST I’m presenting my DBA Mythbusters<\/em> session at SQL Connections for the first time. I’ll hopefully do it as a longer Spotlight Session at SQL PASS this Fall too.<\/p>\n

So a little quickie today as I’m doing 4 sessions in a row.<\/p>\n

Myth #14:<\/strong> clearing the log zeroes out log records<\/em>.<\/p>\n

FALSE<\/em><\/u><\/strong><\/p>\n

The transaction log is *always* zero initialized when first created, manually grown, or auto-grown. Do not confuse this with the process of clearing the log during regular operations. That simply means that one or more VLFs (Virtual Log Files) are marked as inactive and able to be overwritten. When log clearing occurs, nothing is cleared or overwritten. ‘Clearing the log’ is a very confusing misnomer. It means the exact same as ‘truncating the log’, which is another unfortunate misnomer, because the size of the log doesn’t change at all.<\/p>\n

You can read more about zero initialization of the log in my blog post Search Engine Q&A #24: Why can’t the transaction log use instant initialization?<\/a><\/em> and about how log clearing works in my TechNet Magazine article from February 2009:\u00a0Understanding Logging and Recovery in SQL Server<\/a>.<\/p>\n

You can prove this to yourself using trace flag 3004. Turning it on will let you see when SQL Server is doing file zeroing operations (as I described in A SQL Server DBA myth a day: (3\/30) instant file initialization can be controlled from within SQL Server<\/a><\/em>). Turn it on and watch for messages coming during the day – you shouldn’t see anything unless the log grows.<\/p>\n

Here’s a script to show you what I mean:<\/p>\n

\r\nDBCC TRACEON (3004, 3605);\r\nGO\r\n\r\n-- Create database and put in SIMPLE recovery model so the log will clear on checkpoint\r\nCREATE DATABASE [LogClearTest] ON PRIMARY (\r\n    NAME = N'LogClearTest_data', FILENAME = N'D:\\SQLskills\\LogClearTest_data.mdf')\r\nLOG ON (\r\n    NAME = N'LogClearTest_log', FILENAME = N'D:\\SQLskills\\LogClearTest_log.ldf',\r\nSIZE = 20MB);\r\nGO\r\n\r\n-- Error log mark 1\r\nALTER DATABASE [LogClearTest] SET RECOVERY SIMPLE;\r\nGO\r\n\r\nUSE [LogClearTest];\r\nGO\r\n\r\n-- Create table and fill with 10MB - so 10MB in the log\r\nCREATE TABLE [t1] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');\r\nGO\r\nINSERT INTO [t1] DEFAULT VALUES;\r\nGO 1280\r\n\r\n-- Clear the log\r\nCHECKPOINT;\r\nGO\r\n\r\n-- Error log mark 2\r\nALTER DATABASE [LogClearTest] SET RECOVERY SIMPLE;\r\nGO\r\n<\/pre>\n
And in the error log I see:<\/p>\n
\r\n2010-04-13 13:20:27.55 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DBCC TRACEON 3004, server process ID (SPID) 53. This is an informational message only; no user action is required.\r\n2010-04-13 13:20:27.55 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DBCC TRACEON 3605, server process ID (SPID) 53. This is an informational message only; no user action is required.\r\n2010-04-13 13:20:27.63 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Zeroing D:\\SQLskills\\LogClearTest_log.ldf from page 0 to 2560 (0x0 to 0x1400000)\r\n2010-04-13 13:20:28.01 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Zeroing completed on D:\\SQLskills\\LogClearTest_log.ldf\r\n2010-04-13 13:20:28.11 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Starting up database 'LogClearTest'.\r\n2010-04-13 13:20:28.12 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FixupLogTail() zeroing D:\\SQLskills\\LogClearTest_log.ldf from 0x5000 to 0x6000.\r\n2010-04-13 13:20:28.12 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Zeroing D:\\SQLskills\\LogClearTest_log.ldf from page 3 to 63 (0x6000 to 0x7e000)\r\n2010-04-13 13:20:28.14 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Zeroing completed on D:\\SQLskills\\LogClearTest_log.ldf\r\n2010-04-13 13:20:28.16 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Setting database option RECOVERY to SIMPLE for database LogClearTest.\r\n2010-04-13 13:20:29.49 spid53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Setting database option RECOVERY to SIMPLE for database LogClearTest.\r\n<\/pre>\n
The two ALTER DATABASE<\/em> commands serve as markers in the error log. There’s clearly no zeroing occurring from the CHECKPOINT<\/em> between the two ALTER DATABASE<\/em> commands. To further prove to yourself, you can add in calls to DBCC SQLPERF (LOGSPACE)<\/em> before and after the CHECKPOINT<\/em>, to show that the log is clearing when the checkpoint occurs (watch the value in the Log Space Used (%)<\/em> column decrease).<\/p>\n
Now it’s session time here in Vegas! (And there’s no-one to blame but yourself if you’re the Conference Chair and you’ve got an 8am session! :-)<\/p>\n","protected":false},"excerpt":{"rendered":"
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today is the big day – 8am PST I’m presenting […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50,61,98],"tags":[],"class_list":["post-717","post","type-post","status-publish","format-standard","hentry","category-instant-initialization","category-misconceptions","category-transaction-log"],"yoast_head":"\nA SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - 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\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today is the big day – 8am PST I’m presenting […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-14T06:07:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-12T19:50:15+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=\"4 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\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/\",\"name\":\"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-14T06:07:00+00:00\",\"dateModified\":\"2017-07-12T19:50:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records\"}]},{\"@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":"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - 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\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today is the big day – 8am PST I’m presenting […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-14T06:07:00+00:00","article_modified_time":"2017-07-12T19:50:15+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/","name":"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-14T06:07:00+00:00","dateModified":"2017-07-12T19:50:15+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1430-clearing-the-log-zeroes-out-log-records\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (14\/30) clearing the log zeroes out log records"}]},{"@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\/717","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=717"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/717\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=717"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=717"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=717"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}