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!