SQL Server Recovery time is the time it takes for SQL Server to rollback or roll-forward transaction not yet persisted to the database after a service restart. Transactions that are uncommitted are rolled back, and transactions that are committed but not yet persisted are rolled forward.
Depending on the volume of transactions that needs to be handled during recovery, recovery may take a long time to perform. This affects the time it takes for a SQL Server restart. Another example when recovery times become a factor is when a failover is done in a SQL Server cluster.
So how can we know how long the recovery time will be?
Unfortunately, there is no exact way to predict the time a recovery will take. As a guide, try not to restart or failover (assuming, of course, a manual failover) during peak hours when the system is heavily utilized. Remember also to consider batch processes in this, as they tend to issue large transactions.
That said, the latest recovery times can (with a little work) be viewed from the SQL Server error log. If historical recovery times are stored, an educated guess about recovery times can be made.
The system stored procedure (undocumented) xp_readerrorlog can be used to get the events when a recovery is started and completed from the error log:
--RECOVERY START EXEC xp_readerrorlog 0, 1, N'(c) Microsoft Corporation.', NULL, NULL, NULL, N'ASC'
--RECOVERY COMPLETE 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'
The calls to xp_readerrorlog are capsuled into a stored procedure called GetRecoveryTime:
CREATE PROCEDURE dbo.GetRecoveryTime
AS
SET NOCOUNT ON
DECLARE @RecoveryStart DATETIME, @RecoveryStop DATETIME
IF (SELECT OBJECT_ID('tempdb..#TMP_RECOVERY_START')) IS NOT NULL BEGIN
DROP TABLE #TMP_RECOVERY_START
END
IF (SELECT OBJECT_ID('tempdb..#TMP_RECOVERY_STOP')) IS NOT NULL BEGIN
DROP TABLE #TMP_RECOVERY_STOP
END
CREATE TABLE #TMP_RECOVERY_START (LogDate DATETIME, ProcessInfo NVARCHAR(MAX), Text NVARCHAR(MAX))
CREATE TABLE #TMP_RECOVERY_STOP (LogDate DATETIME, ProcessInfo NVARCHAR(MAX), Text NVARCHAR(MAX))
INSERT #TMP_RECOVERY_START EXEC xp_readerrorlog 0, 1, N'(c) Microsoft Corporation.', NULL, NULL, NULL, N'ASC'
INSERT #TMP_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 #TMP_RECOVERY_START
SELECT @RecoveryStop = LogDate FROM #TMP_RECOVERY_STOP
SELECT DATEDIFF(SECOND, @RecoveryStart, @RecoveryStop) AS RecoveryTimeSeconds
GO
