How do I check if a database is in recovery mode SQL Server?

by
How do I check if a database is in recovery mode SQL Server

When a SQL Server database is recovering from a failure or unexpected shutdown, it enters recovery mode. During recovery mode, the database is unavailable for regular use as the SQL Server works to roll back incomplete transactions and restore the database to a consistent state. Therefore, it is important for database administrators to know how to check the recovery status of a SQL Server database.

Using System Stored Procedures

SQL Server provides some system stored procedures that can be used to check the recovery status of a database. The two key stored procedures are:

  • sys.databases
  • sys.database_recovery_status

The sys.databases stored procedure returns information about all databases on an instance of SQL Server, including their recovery model and status. To check if a specific database is in recovery mode, you can query this system table like:

SELECT name, recovery_model_desc, state_desc 
FROM sys.databases
WHERE name = 'MyDatabase'

If the state_desc column returns “RECOVERY_PENDING”, it indicates the database is in recovery mode.

The sys.database_recovery_status stored procedure provides more detailed information about the recovery status of a database. To check a specific database, execute it as follows:

EXEC sys.database_recovery_status 'MyDatabase'

This will return details like the current recovery stage (redo, undo, etc), when recovery started, percentage complete and an estimated finish time if recovery is still in progress.

Using PowerShell

In PowerShell, the Get-DbaDatabase cmdlet can be used to retrieve database information including their recovery status. For example:

Get-DbaDatabase -SqlInstance sqlserver -Database MyDatabase | Select-Object RecoveryModel, Status

Here the Status property will indicate “RecoveryPending” if the database is recovering.

Additionally, the Get-DbaDatabaseRecoveryModel cmdlet returns only the recovery model and status of databases and can be filtered for a specific database:

Get-DbaDatabaseRecoveryModel -SqlInstance sqlserver -Database MyDatabase

Using SQL Server Management Studio (SSMS)

The recovery status of a SQL Server database can also be checked visually using the Object Explorer tab in SSMS:

  1. Expand the SQL Server instance and Databases node
  2. Right click on the database and select Properties
  3. In the Database Properties window, look at the Status field

If the status is “Recovery Pending”, it indicates the database is currently in recovery mode.

Identifying Cause of Recovery

Once you’ve identified that a SQL Server database is in recovery mode, it is helpful to determine what caused it to enter recovery. Some common reasons include:

  • Server was restarted unexpectedly
  • Database was set to OFFLINE and then ONLINE
  • Database files were restored or detached/attached
  • Database was set to EMERGENCY mode
  • Server crashed or a failover occurred

Looking at the SQL Server error log can provide clues on what initiated recovery mode for a database. The log will contain messages about operations like shutting down or restarting the SQL Server service, restoring database files, etc.

Additionally, querying the msdb database’s backupset and restorehistory tables can reveal if database files were recently restored:

SELECT * FROM msdb.dbo.backupset WHERE database_name='MyDatabase'
ORDER BY backup_finish_date DESC

SELECT * FROM msdb.dbo.restorehistory WHERE destination_database_name='MyDatabase' 
ORDER BY restore_date DESC

Monitoring Progress of Recovery

When a SQL Server database is in recovery mode, it goes through several phases as it recovers all transactions and brings the data to a consistent state. These include:

  • Analysis phase – Checks database transactions and performs crash recovery
  • Redo phase – Re-applies changes from transaction log records
  • Undo phase – Rolls back uncommitted transactions
  • Rollback phase – Returns database to previous state if redo fails

You can monitor the progress of these recovery stages using the sys.recovery_status system view:

SELECT database_name, recovery_state, percent_complete  
FROM sys.databases d
JOIN sys.recovery_status r ON d.database_id = r.database_id
WHERE d.name = 'MyDatabase'

The percent_complete field will start at 0 and gradually increase to 100 as recovery nears completion. The recovery_state field indicates the current stage.

You can periodically execute the above query until percent_complete reaches 100 to track the recovery progress.

Allowing Client Connections

By default, a SQL Server database in recovery mode does not allow client connections. Attempts to connect will fail with errors like “Cannot open database MyDatabase. It is in the middle of a restore.”

If you need to allow read-only connections to the database during long-running recovery operations, you can use the ALTER DATABASE command:

ALTER DATABASE MyDatabase SET EMERGENCY

This will put the database in EMERGENCY mode, which permits read access to the available data pages even though recovery is still occurring. Just note that this may return stale or inconsistent data until recovery fully completes.

Forcing Recovery Completion

In some cases, you may want to quickly end the recovery process if it is taking an excessively long time to complete. This can be done by resetting the database recovery state using the ALTER DATABASE command:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

This will immediately terminate recovery and transition the database into a usable state. However, it risks potential data loss or corruption if recovery was not allowed to fully complete.

An alternative is to put the database in EMERGENCY mode for a short time, temporarily allow connections, copy out needed data, then take the database offline and restore it from a backup:

ALTER DATABASE MyDatabase SET EMERGENCY

-- Copy data from database 

ALTER DATABASE MyDatabase SET OFFLINE
RESTORE DATABASE MyDatabase FROM BACKUP

Waiting for Recovery to Finish

If you don’t need to immediately access the database, the best approach is to simply wait for SQL Server to automatically complete the recovery process. You can periodically check the status using the techniques shown earlier until recovery reaches 100% complete.

The amount of time required for recovery depends on how much recovery work needs to be done, like rolling back uncommitted transactions, redoing operations from logs, etc. It could be just a few seconds or minutes for minor crashes, or much longer for major failures or huge transaction volumes.

Be patient and give SQL Server time to ensure your database returns to a proper state. Avoid trying to force database availability until recovery fully finishes.

Troubleshooting Incomplete Recovery

In some unusual cases, recovery may become stuck and never reach completion even after an excessive amount of time. Some potential reasons include:

  • Corruption in database transaction logs or data files
  • Insufficient disk space for restoring logs
  • Recovery components waiting on missing database files
  • System resource bottlenecks slowing down recovery

If recovery seems to stall, there are some things you can try to troubleshoot:

  • Check SQL Server error logs for clues on recovery failures
  • Verify adequate free disk space for data, logs, and tempdb
  • Restart the SQL Server service to reset components
  • Restore transaction logs to an alternate location in case of corruption
  • Consider forcing recovery completion if corrupted or unusable data is acceptable

However, it is best to contact Microsoft Support if recovery persists to get help diagnosing and resolving the underlying problem.

Conclusion

Recovery mode is an important process that helps ensure SQL Server databases maintain consistency and integrity after crashes or failures. As a database administrator, you should familiarize yourself with techniques for monitoring database recovery status and progress. Allow SQL Server time to properly recover databases, but be prepared to take action if recovery stalls or you need urgent database availability.

The system procedures, PowerShell cmdlets and DMVs discussed provide the key tools for checking recovery status and tracking completion percentage. Consult the SQL Server logs and documentation for further troubleshooting if any issues are encountered. With the right knowledge, you can ensure your databases successfully recover and return to operations.