question

kpsenthilkumar86 avatar image
kpsenthilkumar86 asked

SQL Server 2012 Snapshot issue

Hi all, In our organization We run a job every hour that drops and recreate the snapshot of a database. We didn't face any issue till yesterday. But today it took around one hour for the job to get completed, because of this all the applications depending on this database failed to respond. The error log for the job shows a message "Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060)". we found some blocking on the database during the time of job failure. I am just wondering whether a blocking can delay the dropping & creation of snapshot to this extent. Thanks in advance Regards, K.P.Senthil Kumar
snapshot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
If there are people connecting to and reading from the snapshot, then attempting to drop it can be blocked by those connections. The best thing to do if this happens again is go into sys.dm_exec_requests and identify the blocking chain in order to understand why the drop is being prevented.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.