1

We had a maintenance activity done on our SQL Servers and after that, we restarted the services. Now the databases took their sweet time to recover and after recovery, we started some jobs thinking that everything is good. Now one of the job runs was blocked by a spid that has its command as DB STARTUP. Suspiciously, I checked the SQL Server logs and there it was showing that one database was still recovering. However, in the object explorer I am able to see that database as online and also I can query the database. I even did some fake updates and truncated some tables too in that database. So, what recovery is exactly going on here? The databases are in AOAG, if that helps.

2
  • What is output of select @@version, if you have enterprise edition then you have fast recovery in which database can come online after second phase of recovery and in background third phase can continue. I will add the answer after your revert
    – Shanky
    Oct 14 '16 at 17:17
  • It is an enterprise version. Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)
    – karun_r
    Oct 14 '16 at 17:17
2

As confirmed by you the SQL server is of enterprise version and database was not starting after restore so the recovery process can utilize fast recovery. In enterprise edition the database would come online after second phase (this is called Fast recovery which was introduced from SS 2005 onwards) and you would see database as online but still internally third phase, the UNDO, phase is going on and would be accessible completely only after third phase completes. What I mean by completely is For fast recovery, a query may bump into one of the locks being held to allow fast recovery – in which case it will have to wait for that lock to be dropped as UNDO progresses

I suggest you also read

5
  • But the DB STARTUP is blocking only one particular spid. Other operations are unaffected. So this means that the lock is there on the objects that the job is trying to alter?
    – karun_r
    Oct 14 '16 at 19:53
  • Can you tell me what is the operation doing which is being blocked by DB startup ?
    – Shanky
    Oct 15 '16 at 3:12
  • It is a step in a job which truncates some tables. Weird thing is , the DB Startup blocks the job step but it allowed me to truncate those tables manually.
    – karun_r
    Oct 15 '16 at 3:26
  • Can you post the errorlog in the question ?, I need to see information whether database has recovered or not. You can also upload erorrlog on shared drive and share link in the question.
    – Shanky
    Oct 15 '16 at 3:38
  • @karun10 Are you going to share the output ?
    – Shanky
    Oct 15 '16 at 16:56
0

It seems the database is not recovered completely. Please check the SQL server log. Lock will release automatically once database completely recovered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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