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.