Your SQL Server just decided to throw its toys out of the cot and it was right slap bang in the middle of that big index rebuild you were doing, or that mighty transaction. Now you look to carry on where you left off and really hope nobody picked this up. The only problem is that in SSMS you see the database status as “IN RECOVERY”.. What? Luckily SQL server is smart, really smart and it can repair itself when it hit a big boo-boo. Which is all well and good, but nobody wants to hear what happened, just how long until they can get cracking at the server again. Well have no fear, here is a little script that reads the log entries as SQL server gets your Database up and running. The logs look something like this:
Recovery of database ‘myDB’ (7) is 15% complete (approximately 4584 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Great if you can read seconds, but for me, more that say 60 seconds just throws me. So let’s script this a bit.
/* ﷽-*/ DECLARE @ErrorLog AS TABLE(LogDate DATETIME, ProcessInfo VARCHAR(64), Txt VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database' SELECT TOP 5 CONVERT(VARCHAR,T1.LogDate,120) LogDate , LEFT(RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%database%',T1.Txt) - 9),