SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database snapshots can take a long time to create

Database Snapshots Can Take a Long Time to Create

Believe it, it's true!! One of the big selling point for database snapshots is that creating them is almost instantaneous. Since a database snapshot is created as an empty shell, it should be and almost always is a very quick process. What they don't tell you is long running active transactions can extensively delay the creation of the snapshot file.

What's causing my snapshot creation to freeze for long periods of time?

When you create a database snapshot, the snapshot contains all open transactions at the time. As part of the initialization process, it calls the recovery process on the snapshot. Part of the recovery process is to roll back all active transactions in the snapshot (they do not roll back in the actual database). If there are long running transactions -- such as a large index rebuild -- running at the time of the snapshot creation, the rollback can take a really long time.

If it appears that your snapshot creation is frozen, you can check sys.dm_exec_requests, and you should see rollbacks in progress in the database snapshot.

According to Books Online:
Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).


No comments.

Leave a Comment

Please register or log in to leave a comment.