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.
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
Just in case someone finds this, each database in an availability group has a redo thread, and the command is always
DB STARTUP. In fact, that is how you can identify the redo spid associated with each database -
SELECT * FROM sys.dm_exec_requests WHERE command = 'DB STARTUP'.
Additional troubleshooting information is available in the documentation.