Tuesday, January 08, 2008

Transaction log files after reverting from snapshot

Watch out with the transaction log file(s) when you revert a database from a snapshot! A snapshot database has no transaction log itself and when you revert the original database to the snapshot database, it is documented that Sql Server rebuilds the log. It does however not reuse the characteristics of the original transaction log file(s) as one could expect. The actual and maximum size, the autogrowth property and even the number of files are not necessary the same as before reverting. This could get you easily into trouble.

Lets illustrate this with an example, by creating a database SnapshotTest with 2 transaction log files, which will probably not be the same number as is the case for the model database. A snapshot of SnapshotTest with the name SnapshotTest_ss gets created.
Now for the interesting stuff: we revert the database from the snapshot and we investigate the transaction log files as we did earlier. Only the first transaction log file will be present for the SnapshotTest database but not with the same characteristics as before and not even the ones of model's transaction log. The second file is no longer part of the database. Is this desired behavior?

USE [master]
GO
CREATE DATABASE SnapshotTest
ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest.MDF')
LOG ON (NAME = SnapshotTestLog1, FILENAME = 'C:\TEMP\SnapshotTestLog1.LDF')
, (NAME = SnapshotTestLog2, FILENAME = 'C:\TEMP\SnapshotTestLog2.LDF')
GO
EXEC sp_helpdb SnapshotTest
GO
CREATE DATABASE SnapshotTest_ss ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest_ss.MDF')
AS SNAPSHOT OF SnapshotTest
GO

RESTORE DATABASE SnapshotTest
FROM DATABASE_SNAPSHOT = 'SnapshotTest_ss'
GO
EXEC sp_helpdb SnapshotTest
GO
USE [master]
GO
DROP DATABASE SnapshotTest_ss
GO
DROP DATABASE SnapshotTest

Don't forget to clean up the database file that is left behind on the file system - it's no longer part of a database so it's not removed when you drop the database.