We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL 2005 the Job that drops the snapshots used to finish in few seconds. But once the server has been upgraded to 2012, the same job takes close to an hour to complete.

The log files for the job contains a message

"Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%."

This message is repeated several times. While monitoring the underlying database, we have found some blocking on the database during that time. But if I am correct, a blocking on the underlying database should not delay a statement to drop its snapshot.

May I know if I am correct? If so, what could be the reasons for this delay after upgrading to 2012? Any missing settings at the server or database level? Thanks.


Can you please confirm the Compatibility level of the database you upgraded? (Right-click the database -> Properties -> Options -> Compatibility level). If this is set to 2005, please don't change it without thoroughly testing your applications, etc. as it could definitely break your environment! Just curious as to the value here.

Also, I did some work with a test system and didn't see the behavior you describe. Even if I had an open transaction using the DB snapshot I could drop it from another session and the query running on the snapshot would immediately fail (rather than block me from dropping the DB snapshot).

I would recommend taking a closer look at the disk where the DB snapshot is created (since dropping the DB snapshot physically deletes the snapshot file created). There possibly could be speed issues there causing the delete of the snapshot file to simply take close to an hour...or there could be contention at the file level (something trying to back up the DB snapshot file while you're trying to drop the DB snapshot, virus tools scanning the DB snapshot file, etc.).

Not the answer you're looking for? Browse other questions tagged or ask your own question.