2018-09-07

Estimating SQL Server Recovery Time

Every time when an SQL Server instance is restarted, no matter it is a planned restart or cluster failover, SQL Server undergoes the recovery process, which takes time to rollback uncommitted transactions, and roll-forward committed transactions (hardened to log file, but not yet applied to data file) during the server restart. Depending on the workload on your databases and how many databases are there, recovery may take a long time to run. There is no any exact way to calculate the time a recovery will take, however, you can get an estimation based on the last recovery that you SQL Server took. Below sql script employs the undocumented system stored procedure, xp_readerrorlog, to get the latest recovery start time and completion time, and calculates the last recovery time:
SET NOCOUNT ON;
DECLARE @RecoveryStart datetime, @RecoveryStop datetime;
DECLARE @tbl_RECOVERY_START table (LogDate datetime, ProcessInfo nvarchar(max), [Text] nvarchar(max));
DECLARE @tbl_RECOVERY_STOP table (LogDate datetime, ProcessInfo nvarchar(max), [Text] nvarchar(max));
INSERT @tbl_RECOVERY_START EXEC xp_readerrorlog 0, 1, N'(c) Microsoft Corporation.', NULL, NULL, NULL, N'ASC';
INSERT @tbl_RECOVERY_STOP EXEC xp_readerrorlog 0, 1, N'Recovery is complete. This is an informational message only. No user action is required.', NULL, NULL, NULL, N'DESC';
SELECT @RecoveryStart = LogDate FROM @tbl_RECOVERY_START;
SELECT @RecoveryStop = LogDate FROM @tbl_RECOVERY_STOP;
SELECT DATEDIFF(second, @RecoveryStart, @RecoveryStop) AS RecoveryTimeSeconds;