2

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.

closed as off-topic by Max Vernon, Marco, Philᵀᴹ, dezso, RLF Jan 26 '17 at 22:54

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Marco, Philᵀᴹ, dezso, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

0

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.