Bug: reverting from a database snapshot shrinks the transaction log to 0.5MB

I’ve blogged about problems with database snapshots a few times previously (see my Database Snapshots category) but here’s a new one that I’ve been meaning to blog for a while that you need to know about.

One of the cool uses of database snapshots is to be able to create one, make a bunch of changes to the source database, and if you want to go back to the prior state of the source database then instead of doing a potentially long restore operation from scratch, you can revert to the snapshot. In other words, you’re winding the source database back in time to the instant that you created the database snapshot. Sounds cool, right?

It is – except for a hidden behavior that I discovered. When you revert to the database snapshot, the transaction log file of the source database is ripped out and replace with a 0.5MB log file with two 0.25MB VLFs. This means that after the revert you’re going to have to manually reset the log file to the size it used to be, waiting for the zero initialization of the log file to occur (see Search Engine Q+A #24: Why can’t the transaction log use instant initialization?), or if you don’t realize this behavior has occurred, your log will grow itself and your workload will suffer performance hits waiting for the log to zero initialize during each log growth.

This is horrible behavior and is a nasty bug.

Let me show you on SQL Server 2008R2 SP1. Using the SalesDB database that you can download from our Resources section (see top section of that link), I’ll restore the database, create a snapshot, and examine the log file of the SalesDB database:

USE [master];
GO
IF DATABASEPROPERTYEX (N'SalesDB_Snapshot', N'Version') > 0
    DROP DATABASE [SalesDB_Snapshot];
GO

RESTORE DATABASE [SalesDB] FROM DISK = N'D:\SQLskills\Backups\SalesDB.bak'
WITH STATS = 10, REPLACE;
GO

-- Create the snapshot
CREATE DATABASE [SalesDB_Snapshot] ON (
    NAME = N'SalesDBData',
    FILENAME = N'C:\SQLskills\test\SalesDBData.mdfss')
AS SNAPSHOT OF [SalesDB];
GO

DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO
FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ----------
2           10420224             8192                 0           0           64     0
2           10420224             10428416             0           0           64     0
2           10420224             20848640             373         2           64     0
2           10674176             31268864             0           0           128    0

Database Name  Log Size (MB) Log Space Used (%) Status
-------------- ------------- ------------------ -----------
master         6.742188      21.86414           0
tempdb         1.492188      52.35602           0
model          11.49219      12.27056           0
msdb           23.80469      11.8149            0
Mail           4.617188      91.64552           0
SalesDB        39.99219      8.934851           0

Now I’ll revert to the snapshot and examine the log again:

RESTORE DATABASE [SalesDB] FROM DATABASE_SNAPSHOT = N'SalesDB_Snapshot';
GO

DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO
FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ----------
2           253952               8192                 1398        2           64     0
2           253952               262144               0           0           0      0

Database Name  Log Size (MB) Log Space Used (%) Status
-------------- ------------- ------------------ -----------
master         6.742188      21.92932           0
tempdb         1.492188      52.35602           0
model          11.49219      12.27056           0
msdb           23.80469      11.8149            0
Mail           4.617188      91.64552           0
SalesDB        0.484375      45.3629            0

WOW! Check out the VLF sizes and the total log size.

I’ve been asked before whether the revert is copying the log from the model database, but you can clearly see that it’s not as model’s log is 11.5MB. And none of the databases on my instance have VLFs with sequence numbers anywhere near 1398 (the FSeqNo column in the DBCC LOGINFO output) so I’ve got no idea where that is coming from.

This behavior exists in all versions up to and including SQL Server 2016.

Be careful out there!

5 thoughts on “Bug: reverting from a database snapshot shrinks the transaction log to 0.5MB

  1. At my last job, we used snapshots extensively for testing and development, so I wrapped up creating/restoring to snapshots in a couple of SPs – my restore one looked up the log file size before it did the restore, and re-sized the transaction log before it let anyone else back in the database. Of course it would be simpler if they just made it work that way anyway.

    Is there some reason i’m not thinking of that they couldn’t just keep the log file that existed before the restore to the snapshot, and just clear the log?

  2. There was thread on the Technet forums (http://social.technet.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/540aa6c7-6fd4-49bb-8dba-6506768d492d)
    that discussed this and resulted in a Connect Item being posted.

    My tests indicated that the behaviour was identical with other cases where the log is rebuilt (eg ATTACH_REBUILD_LOG) As such is appeared to have been by design and was simply reusing code to rebuild log.

    Looks like it was really a bug and the thread needs to be updated.

  3. Hey, Paul.
    It’s great that you’re making people aware of this behaviour. I’ve had my own headache with this.
    I am still a bit unsure whether this really is a bug or actually intented behaviour by Microsoft.
    I started a forum thread asking about this some time ago, and it was from there I got directed to this blog entry :)

    Read if you like and perhaps you could decide better.
    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/540aa6c7-6fd4-49bb-8dba-6506768d492d/?prof=required

    Cheers,
    Cloxy

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.