The Curious Case of… a truncated log file after a revert from snapshot

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I had an email question last week from someone who was very concerned to see a truncated log file after a revert from snapshot. They thought they’d come across a SQL Server bug and wanted to know if I’d seen this before.

Well, the answer is ‘yes’, because that’s how SQL Server has behaved since SQL Server 2005 introduced database snapshots. Whenever you perform a revert to snapshot, the log file is discarded and a new one created with two 256KB VLFs.

This behavior is by design, but understandably surprises people. The idea is that the log does have to be reset during a revert, because the database is being wound backwards in time physically, rather than by rolling back transactions. You’d think that SQL Server would recreate the log at the size it was previously, but the thinking was (when we built the feature) that if the previous log size was very large, recreating it would potentially take a long time, as the log must be zero-initialized on first allocation in the file system. So, it just whacks the log down to two small VLFs, which means after a revert you’ll need to manually grow the log again.

Here’s an example using SQL Server 2019:

USE [master];
GO

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

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

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

Now, looking at the log file size (using the old DBCC commands instead of the new DMVs so people playing with this on older versions can follow along) and some of the fields removed from the results for clarity:

DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO

RecoveryUnitId FileId      FileSize             
-------------- ----------- -------------------- 
0              2           10420224             
0              2           10420224             
0              2           10420224             
0              2           10674176             

Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
master        1.992188      43.77451           0
tempdb        7.992188      6.042278           0
model         19.61719      6.586021           0
SalesDB       39.99219      18.78174           0

Four VLFs of about 10MB each in the SalesDB database. Now I’ll revert to the snapshot and check again:

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

DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO

RecoveryUnitId FileId      FileSize             
-------------- ----------- -------------------- 
0              2           253952
0              2           253952           

Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
master        1.992188      43.77451           0
tempdb        7.992188      6.042278           0
model         19.61719      6.586021           0
SalesDB       0.484375      60.28226           0

You can see that the log has been reset to two 256KB VLFs.

Bottom line: it’s expected behavior you need to be aware of. It’s also a neat way to reset the VLFs in a log if someone created the initial log to be enormous and you want to resize it to be a lot smaller, as there’s no other way of reducing the size of the first couple of VLFs.