January 24, 2012 at 9:00 am
I maintain a 3rd party app on SQL 2008R2 SP1, with DBCC CHECKDB scheduled to run each night such that it completes before the index maintenance fires off.
In my SQL log, every few days I'm seeing an entry with the CHECKDB start time that reads, "Recovery completed for database XXX (database ID n) in 1 second(s) (analysis 0 ms, redo 0 ms, undo 1 ms.) This is an informational message only. No user action is required."
After hearing horror stories about databases "in recovery", the entry makes me a little nervous. Is this a normal part of DBCC CHECKDB, and if so, why don't I see it every time?
January 24, 2012 at 9:07 am
Firstly, "In recovery" is a normal process that all of your databases go through every time SQL starts up, so there's nothing horrific about it.
Is this the combination of messages that you're seeing?
1 transactions rolled back in database 'Testing' (15). This is an informational message only. No user action is required.
Recovery completed for database Testing (database ID 15) in 2 second(s) (analysis 0 ms, redo 669 ms, undo 1189 ms.) This is an informational message only. No user action is required.
If so, that's SQL recovering the hidden database snapshot that CheckDB uses. It's not the actual database that is getting transactions rolled back and recovered, it's a database snapshot. CheckDB uses that to get a consistent view of the database without having to take long-lasting table locks.
You would only get that if there were open transactions in the database at the point that the snapshot is created. If there aren't any open transactions, then nothing needs to be rolled back in the context of the snapshot, hence there's no recovery messages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 9:36 am
Thanks Gail, that makes sense--my googling was leading me to the snapshot process, but I couldn't quite find the connection to this specific message.
I'm not actually getting a message about the transaction being rolled back--here's the full series:
FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
Recovery completed for database XXX (database ID n) in 1 second(s) (analysis 0 ms, redo 0 ms, undo 1343 ms.) This is an informational message only. No user action is required.
DBCC CHECKDB (XXX) WITH no_infomsgs executed by HOST\sqlagent found 0 errors and repaired 0 errors. Elapsed time: 1 hours 21 minutes 13 seconds. Internal database snapshot has split point ...
I guess I should trust the "This is an informational message only. No user action is required." bit, but what kind of admin would I be if I trusted everything my server told me? 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply