My Favorite Blogs

Saturday, February 5, 2011

Error when the database snapshot refreshing [Error: 913, Severity: 16, State: 1]

Today on one of our SQL Instance I observed that the below event was occurring last couple of days and I’ve been asked to look into it. I found that there is pattern in the event occurrence, it happened every 5minutes continuously. I started the sql trace from my jump box and trapped logs for 30 minutes and found that on the instance there is a job which is refreshing a database snapshot every 5 minutes and the same time an user sproc hits the same snapshot and trying to pull some data which causing to raise this event.

The snapshot job scheduled to run every minute and the user calling the sproc on the snapshot at minute, there is just 1 second difference but the job takes 30 seconds to complete and the database snapshot is inaccessible state at that time when the user trying to access. That’s when the below event occurs.

SQL Events

2011-02-05 09:25:02.250 spid59 Error: 913, Severity: 16, State: 1.

2011-02-05 09:25:02.250 spid59 Could not find database ID 8. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur

To avoid these logs in the SQL errorlog either I’ve to request the user to change his job schedule or I can change the snapshot refresh job schedule. In this case I changed in the snapshot refresh job schedule to start 40 seconds after so the snapshot can be accessed by the user’s sproc and after refresh job will start.

Now the events completely stopped occurring.

No comments:

Post a Comment