Database Restores – Zeroing Out Log Files

IFI_TwitterQuestionI recently came across a question on the #sqlhelp Twitter feed that I thought was interesting.  The question pertains to whether or not SQL Server zero outs the log file when doing a restore on an existing database.

We already know that even with instant file initialization, the log file is always zeroed out upon creation or file growth.  But what about a restore?  Given the restore is a page by page copy of the data, shouldn’t it just over lay the existing file?

Let’s find out!

In order to test this, we will:

  1. Create a new database and a new table
  2. Insert some data into the new table
  3. Take a backup of the database
  4. Truncate the table
  5. Restore the database
  6. Look at the logs to see what is happening

Let’s get started!

Now that we have inserted some data, the log file should be a decent size.   You can confirm the file size by looking at the physical LDF file within Windows or you can use this script:


We know now that the log file has some size to it, let’s take another backup of it and then truncate the table.

Turning on two specific trace flags will help us see some of the magic that takes place during the restore.  Keep in mind that truncating the table is just a way to confirm that the restore worked as expected.  

Trace Flag 3004: this flag will allow you see when SQL Server is doing zeroing out operations.  This works for both the data files as well as log files.

Trace Flag 3605: this flag will output results of certain DBCC commands to the SQL Server log.  It’s very similar to trace flag 3604 which outputs the results of certain DBCC commands to the results window.

Note that both of these trace flags are undocumented so you won’t find specific information about them directly from Microsoft.  However, there are several blogs on the internet that discuss them.

With those trace flags on, we can now look into the SQL Server log and see what it’s doing.


We can clearly see that SQL Server zeroed out the pages required for the log file. This shows that even if the database is already present on the server, the log file gets zeroed out when doing a restore.

As we have shown here, if you have to restore a database where the log file has grown to a significant size, SQL Server will have to zero out the pages for it.  This could take a significant amount of time to do so.  If you find yourself in this situation, please make sure that you plan accordingly.

As a final clean up, make sure that the trace flags are turned off.  If they are left on you would potentially put a lot of restore information into the log file.  Let’s also drop the database to clean everything up.




© 2015, John Morehouse. All rights reserved.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.