If your server has crashed or the SQL service has failed during a transaction, you may see that within SQL Server Management Studio that your database is marked as "In Recovery".
You can calculate how long the recovery will take by running the following query against the database:
BEFORE SQL Server 2012
DECLARE @DBName VARCHAR(64) = 'databasename'
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]
FROM @ErrorLog ORDER BY [LogDate] DESC
DECLARE @DBName VARCHAR(64) = 'databasename'
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]
FROM @ErrorLog ORDER BY [LogDate] DESC
You will get a result similar to the following:
Jan 14 2015 9:35AM 33% 0.1 0.00166666666666667 Recovery of database 'DATABASE_NAME' (13) is 33% complete (approximately 6 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Jan 14 2015 9:35AM 27% 0.1 0.00166666666666667 Recovery of database 'DATABASE_NAME' (13) is 27% complete (approximately 6 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.