When a SQL database goes into recovery pending mode, it can be concerning and disruptive. However, there are steps you can take to get your database back online. Recovery pending indicates that the database is waiting for rollback or redo actions before allowing connections. This often occurs after an unexpected shutdown, power loss, or storage failure. The good news is that recovery pending is a protective state, giving you the chance to repair any potential inconsistencies before use. With some troubleshooting, you can have your database up and running again quickly.
What causes recovery pending status?
There are a few common triggers for recovery pending status:
- Unexpected shutdown – If the database server crashes, loses power, or is forcefully shut down, recovery will be needed on restart.
- Storage failure – Issues with storage like a failed hard drive can put the database in pending mode.
- Transaction log corruption – If transaction logs are damaged, the database won’t be able to recover properly.
- Accessing database files – Attempting to access database files manually during operation can require pending recovery.
- Data file corruption – Any corruption in the data files will need to be rolled back or recovered before use.
Essentially any event that prevents the database from shutting down cleanly can lead to recovery pending status. The database is designed this way to prevent further issues from developing by requiring admins to investigate and resolve any problems before bringing it back online.
How can I take a SQL database out of recovery pending status?
Taking a SQL database out of recovery pending mode involves identifying and resolving what caused the issue in the first place. Here are the general steps:
- Restart the SQL Server service – Often a restart will allow pending recovery actions to finish.
- Identify the database with pending status – Query sys.databases to see which database(s) are affected.
- Check error logs – The error log will provide details on what issues were encountered before recovery pending state.
- Run DBCC CHECKDB – This will indicate any corruption or inconsistencies with the database files.
- Repair any corruption or damage – Use DBCC repair options if needed based on CHECKDB results.
- Restore from backup – If repair is not possible, restore the database from a known good backup.
- Restart SQL Server – This will bring the database back online if recovery actions succeeded.
Additionally, there are a few other recovery options that may help:
- Detach and reattach the database – This resets logs and file paths.
- Rebuild the master database – An issue here can put all databases into pending mode.
- Start up using minimal configuration – Start SQL Server with just -f to rule out third party issues.
Isolating what initially put the database into recovery pending will point you towards the proper resolution. Address any file corruption, hardware failures, or transaction log errors, and the database should be able to recover.
Why does my SQL database have recurring recovery pending status?
If your SQL database keeps entering recovery pending mode during restarts, there is likely an underlying issue causing the problem. Some potential reasons for recurring recovery pending status include:
- Transaction log file full – The log cannot truncate if it is full, blocking recovery.
- Hardware problems – Flaky memory, disks, or processors will lead to repeat issues.
- Database file corruption – CHECKDB will help identify any recurring corruption.
- System database corruption – If master, model, or msdb are corrupted, other databases cannot start.
- Transaction log corruption – Corrupted logs must be repaired for recovery to work.
- Service account permissions – The SQL Service account needs modify permissions on certain files.
Looking at the SQL error log and results of DBCC CHECKDB can reveal patterns pointing to the root cause. Addressing recurring problems at the source – like resolving hardware issues, freeing transaction log space, or repairing corrupted databases – will eliminate repeat recovery pending scenarios.
How can I tell if my database is corrupted when in recovery pending?
To check for database corruption when in recovery pending mode:
- Run DBCC CHECKDB against the affected database.
- Check the output for any messages about corruption or damage.
- Look for specific DBCC error codes tied to corruption, like:
- 8928 – Page checksum failure
- 8929 – Page torn error
- 8967 – Database metadata corruption
- 8968 – Table data corruption
- A message of “repair_rebuild is the minimum repair” indicates corruption.
- If CHECKDB itself fails, this points to database corruption.
CHECKDB will report on any corruption in database pages, metadata, indexes, allocation structures, and other areas. If it detects corruption, follow up with DBCC repair options to fix the issues. The depth of CHECKDB scans can also be increased using WITH EXTENDED_LOGICAL_CHECKS for more rigorous analysis if needed.
Can I start up SQL Server without going through recovery pending?
Recovery pending is designed to protect SQL Server from being brought fully online before potential inconsistencies are addressed. However, in certain scenarios you may need to start up the instance without going through recovery. This can be done in two ways:
- Start in single-user mode – This uses the -m startup parameter to only allow one admin connection and avoid recovery pending.
- Start in minimal configuration mode – The -f parameter starts SQL Server without most services, allowing access to corrupted databases.
Single-user mode will let you connect and investigate issues while holding off full recovery. Minimal configuration mode goes further by starting up with barely any services or databases active. Note that neither option actually resolves the underlying problem – they just allow you to start SQL Server to dig into the cause.
Can I rebuild the master database if it is corrupted and in recovery pending?
If the master system database is corrupted, it can put all databases into recovery pending as they are unable to access master. To rebuild master:
- Start the SQL Service in single-user mode using -m.
- Identify that master is corrupted based on error logs or CHECKDB.
- Stop the SQL Service.
- Rebuild master from a backup using STARTUP REBUILD_MASTER.
- Restart SQL Server, which will take master out of pending recovery.
- Bring each database online by restoring or repairing as needed.
This process lets you rebuild the crucial master database through alternative methods when its corruption causes widespread recovery issues. With master rebuilt, the other databases can then be individually addressed to bring the instance back online.
Should I be concerned if my database is stuck in recovery pending?
If a SQL database remains stuck in a recovery pending state for an extended time, it indicates a significant problem requiring intervention. Some scenarios where prolonged pending recovery warrants concern:
- Pending status persists across server restarts – This points to an underlying issue blocking recovery.
- CHECKDB cannot run successfully – Inability to run consistency checks indicates extensive corruption.
- DBCC repair options fail – If repairs are unsuccessful, manual database rebuild may be needed.
- Transaction log cannot be truncated – A full, damaged log file will stall recovery.
- Multiple databases in perpetual pending mode – Shows an instance level configuration or hardware problem.
Allow some time for recovery actions to complete after a restart. But if pending status continues for hours or days, investigate deeper. The database may require restore from backup or rebuild to become functional again.
Can I restore a SQL database backup if the current database is stuck in recovery pending?
If a SQL database remains in recovery pending status indefinitely, restoring from a known good backup can help. To replace the database using backup:
- Take the corrupted database offline.
- Restore the most recent full backup you have available.
- Restore any subsequent differential backups if you have them.
- Replay transaction log backups made since the last full or differential backup.
- Bring the database online, which will now recover from the restored files.
Before restoring, make sure you have the backups needed to fully roll the database forward to the desired restore point. The series of restores will rebuild the database from scratch, avoiding the issues plaguing the current corrupted version.
Can data be recovered if a database in pending mode fails to come online?
If a SQL database in recovery pending mode suffers from unrecoverable corruption or damage, data recovery is still possible in many cases. Some options to retrieve data if the database itself cannot be brought online include:
- Restore an older backup – Even an outdated backup can provide access to historical data.
- Pull data from replicas – If replication or mirroring is active, a replica can be a data source.
- Extract data from transaction logs – Log analysis tools can pull data from intact log segments.
- Use file-level restore – The SQL page structure can allow file content recovery.
- Use raw file backup services – Disk backups at the operating system level can provide database files.
- Attach a copy of physical files – Offline access to database files may allow data views or extraction.
While an unrecoverable database itself may remain offline, the data it contains is rarely lost completely as long as backups, replicas, transaction logs, or database files are available.
Conclusion
SQL databases ending up stuck in recovery pending mode can certainly be disruptive. However, this state is designed to protect your data from being used in a potentially corrupted form. By following SQL Server restart procedures, running consistency checks, addressing any corruption, and restoring from backup where needed, you can get your database back online and accessible again. Knowing the common causes of pending status, options for investigation when it occurs, and data recovery techniques for worst case scenarios will help you deal with this issue and limit any business impact.