{"id":1023,"date":"2008-08-25T00:23:47","date_gmt":"2008-08-25T00:23:47","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-25-Why-isnt-my-log-backup-the-same-size-as-my-log.aspx"},"modified":"2013-01-01T21:06:23","modified_gmt":"2013-01-02T05:06:23","slug":"search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/","title":{"rendered":"Search Engine Q&A #25: Why isn’t my log backup the same size as my log?"},"content":{"rendered":"


\n

I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here<\/a>.<\/P>
\n

Continuing on the transaction log theme of the last few Search Engine Q&A posts, this one addresses a question I’ve heard a few times, most recently on an MVP discussion group. Let me paraphrase:<\/P>
\n


\n

If I have a transaction that inserts a huge amount of data, the transaction log grows to 50-GB. I then rollback the transaction. When I take the next log backup, it’s way smaller than 50-GB. What’s going on?<\/P><\/BLOCKQUOTE>
\n

Let’s see if we can repro the scenario. I’ve created a database with a 500-MB data file and a 1-MB log file, with 100-MB and 1-MB auto-growth intervals. I want the log to be as small as possible and to grow in small chunks so I can see just how much it *has* to grow by, rather than having a large growth size. Then I set the recovery mode to full and took a database backup to make sure the log won’t truncate until it’s backed up.<\/P>
\n


\n

CREATE<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> LogSizeTest <\/FONT>ON
   <\/FONT><\/FONT>(<\/FONT>NAME<\/FONT> =<\/FONT> N‘LogSizeTest’<\/FONT>,
   <\/FONT><\/FONT>FILENAME<\/FONT> =<\/FONT> N‘C:\\SQLskills\\LogSizeTest.mdf’<\/FONT>,
   <\/FONT><\/FONT>SIZE<\/FONT> =<\/FONT> 512MB,
   <\/FONT><\/FONT>FILEGROWTH<\/FONT> =<\/FONT> 100MB)
<\/FONT><\/FONT>LOG<\/FONT> ON<\/FONT> 
   <\/FONT>(<\/FONT>NAME<\/FONT> =<\/FONT> N‘LogSizeTest_log’<\/FONT>,
   <\/FONT><\/FONT>FILENAME<\/FONT> =<\/FONT> N‘C:\\SQLskills\\LogSizeTest_log.ldf’<\/FONT>,
<\/FONT><\/FONT>   SIZE<\/FONT> =<\/FONT> 1MB,
   <\/FONT>FILEGROWTH<\/FONT> =<\/FONT> 1MB);
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

ALTER<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> LogSizeTest <\/FONT>SET<\/FONT> <\/FONT>RECOVERY<\/FONT> <\/FONT>FULL<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

BACKUP<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> LogSizeTest <\/FONT>TO<\/FONT> <\/FONT>DISK<\/FONT> <\/FONT>=<\/FONT> <\/FONT>‘C:\\SQLskills\\LogSizeTest.bak’<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

Let’s check the size of the log:<\/FONT><\/FONT><\/P>
\n


\n

DBCC<\/FONT><\/FONT> SQLPERF <\/FONT>(<\/FONT>LOGSPACE<\/FONT>);
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

Database Name  Log Size (MB) Log Space Used (%) Status
————– ————- —————— ———–
LogSizeTest    0.9921875     36.66339           0
<\/P><\/FONT><\/FONT><\/FONT><\/BLOCKQUOTE>
\n

This gives back info for all databases, I’ve trimmed down the output just for the LogSizeTest<\/FONT> database.<\/FONT><\/FONT><\/P>
\n

Now I’m going to create a table, start an explicit transaction and add about 500-MB of info to the table.<\/FONT><\/FONT><\/P>
\n


\n

USE<\/FONT><\/FONT> LogSizeTest<\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT>SET<\/FONT><\/FONT> <\/FONT>NOCOUNT<\/FONT> <\/FONT>ON<\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT><\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> Test <\/FONT>(<\/FONT><\/FONT>c1 <\/FONT>INT<\/FONT> IDENTITY<\/FONT>, <\/FONT><\/FONT>C2 <\/FONT>CHAR<\/FONT> (<\/FONT>8000)<\/FONT> DEFAULT<\/FONT> (<\/FONT>REPLICATE<\/FONT> (<\/FONT>‘a’<\/FONT>,<\/FONT> 8000)));
<\/FONT><\/FONT>GO<\/P><\/FONT>
\n

BEGIN<\/FONT><\/FONT> <\/FONT>TRAN<\/FONT>;
<\/FONT><\/FONT>GO<\/P><\/FONT>
\n

DECLARE<\/FONT><\/FONT> @count <\/FONT>INT<\/FONT>;
<\/FONT><\/FONT>SELECT<\/FONT><\/FONT> @count <\/FONT>=<\/FONT> 0<\/FONT>;
<\/FONT><\/FONT>WHILE<\/FONT><\/FONT> <\/FONT>(<\/FONT>@count <\/FONT><<\/FONT> 64000<\/FONT>)
<\/FONT><\/FONT>BEGIN
   <\/FONT>INSERT<\/FONT> INTO<\/FONT> Test DEFAULT<\/FONT> VALUES<\/FONT>;
   <\/FONT><\/FONT>SELECT<\/FONT> @count =<\/FONT> @count +<\/FONT> 1;
<\/FONT><\/FONT>END<\/FONT><\/FONT>;
<\/FONT><\/FONT>GO<\/P><\/FONT><\/FONT><\/FONT><\/BLOCKQUOTE>
\n

Checking the log file size again gives:<\/FONT><\/FONT><\/P>
\n


\n

DBCC<\/FONT><\/FONT> SQLPERF <\/FONT>(<\/FONT>LOGSPACE<\/FONT>);
<\/FONT>GO<\/FONT><\/P>
\n

<\/FONT>Database Name  Log Size (MB) Log Space Used (%) Status
————– ————- —————— ———–
LogSizeTest    703.9922      99.98737           0<\/FONT><\/P><\/BLOCKQUOTE>
\n

The log size has grown to about 700-MB, way more than the size of the data I was inserting, and it’s completely full. Now let’s rollback the transaction and check the log size again.<\/FONT><\/FONT><\/P>
\n


\n

ROLLBACK<\/FONT><\/FONT> <\/FONT>TRAN<\/FONT><\/FONT>;
<\/FONT>GO<\/FONT><\/P>
\n

DBCC<\/FONT><\/FONT> SQLPERF <\/FONT>(<\/FONT>LOGSPACE<\/FONT><\/FONT>);
<\/FONT>GO<\/FONT><\/FONT><\/P>
\n

Database Name  Log Size (MB) Log Space Used (%) Status
————– ————- —————— ———–
LogSizeTest    703.9922      85.21268           0<\/FONT><\/P><\/FONT><\/FONT><\/FONT><\/BLOCKQUOTE>
\n

The size of the log file is the same, but the percentage used has actually gone down! How can that happen? Let’s take a backup and checkout it’s size:<\/FONT><\/FONT><\/P>
\n


\n

BACKUP<\/FONT><\/FONT> <\/FONT>LOG<\/FONT> LogSizeTest <\/FONT>TO<\/FONT> <\/FONT>DISK<\/FONT> <\/FONT>=<\/FONT> <\/FONT>‘C:\\SQLskills\\LogSizeTest_log.bck’<\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT>RESTORE<\/FONT><\/FONT> <\/FONT>HEADERONLY<\/FONT> <\/FONT>FROM<\/FONT> <\/FONT>DISK<\/FONT> <\/FONT>=<\/FONT> <\/FONT>‘C:\\SQLskills\\LogSizeTest_log.bck’<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
\n

The BackupSize<\/FONT> in the output from the RESTORE HEADERONLY<\/FONT> is 631454208, which is 602.2-MB. Taking the numbers from the DBCC SQLPER<\/FONT>F output above, 85.21268% of 703.9922-MB is 599.89-MB – so the backup is roughly the same size as the used transaction log. That’s what I’d expect, but why is it smaller than the total size of the transaction log?<\/P>
\n

So what’s going on? Why did the transaction log need to grow so much larger than it needed to, and why did the percentage used actually *drop* after the transaction rolled back?<\/P>
\n

The answer is in the way the transaction log works. Whenever a logged operation occurs in a transaction, there is some transaction log space reserved<\/EM> in case the transaction rolls back. The idea is that there’s always enough space available in the transaction log for a transaction to roll back, without having to grow the transaction log and potentially have that fail. If a transaction could not roll back successfully because the log didn’t have enough space, the database would become transactionally inconsistent, would be taken offline and the state changed to SUSPECT<\/FONT>.<\/P>
\n

The behavior we saw was the Storage Engine reserving transaction log space for a potential roll back. When the roll back occured, the transaction log records necessary to undo the effects of the transaction (called compensation log records<\/EM>) are created and written to the log. The issue is that they usually don’t take up as much space as the Storage Engine reserved, as it tends to be very conservative in its estimates of how much log space to reserve, to avoid the potential for SUSPECT<\/FONT> databases. This explains the difference between the various sizes and percentages we saw above.<\/P>
\n

The Storage Engine code to do the reservations is quite interesting – I remember fixing a couple of bugs in it during SQL Server 2000 development in 1999 while I was getting to know the internals of the logging and recovery system before tackling some of the (since removed) log-reading code in DBCC CHECKDB in SQL Server 2000.<\/P>
\n

Anyway, there you have it. Log space reservation is the answer, and is also one of the reasons why it can be tricky to estimate how large a transaction log should be when a database is created.<\/P><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,31,38,78,98],"tags":[],"class_list":["post-1023","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-database-maintenance","category-example-scripts","category-search-engine-q-and-a","category-transaction-log"],"yoast_head":"\nSearch Engine Q&A #25: Why isn't my log backup the same size as my log? - 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\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&A #25: Why isn't my log backup the same size as my log? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-08-25T00:23:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T05:06:23+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\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/\",\"name\":\"Search Engine Q&A #25: Why isn't my log backup the same size as my log? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-08-25T00:23:47+00:00\",\"dateModified\":\"2013-01-02T05:06:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&A #25: Why isn’t my log backup the same size as my log?\"}]},{\"@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":"Search Engine Q&A #25: Why isn't my log backup the same size as my log? - 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\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #25: Why isn't my log backup the same size as my log? - Paul S. Randal","og_description":"I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/","og_site_name":"Paul S. Randal","article_published_time":"2008-08-25T00:23:47+00:00","article_modified_time":"2013-01-02T05:06:23+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\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/","name":"Search Engine Q&A #25: Why isn't my log backup the same size as my log? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-08-25T00:23:47+00:00","dateModified":"2013-01-02T05:06:23+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-25-why-isnt-my-log-backup-the-same-size-as-my-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&A #25: Why isn’t my log backup the same size as my log?"}]},{"@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\/1023","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=1023"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1023\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}