SQL Server Database Recovery refers to the method of restoring a database to a consistent state. It becomes an inevitable necessity if the SQL database becomes inaccessible due to severe damage, corruption, user error, or a system failure. To execute this method, the admin uses data backups and transaction logs.
Among the major tasks performed as a part of this operation are rolling back incomplete transactions and reapplying committed transactions to ensure complete data consistency and integrity. However, several factors, like corrupted logical database files, insufficient disk space, abrupt system shutdown, etc., can obstruct the database recovery process and show errors.
Here, we will discuss in detail these SQL database recovery errors arising due to incomplete transactions, along with their manual and automated resolution.
How to determine the state of the database under recovery?
To fix the errors, the foremost step is to be aware of them, which makes it crucial to know about the current state of the database during the recovery process. You can determine the state by using the sys.databases view in the SQL Server Management Studio or any other integrated environment. Here is the query for the same:
SELECT name, state_desc
FROM sys.databases
where name = ‘TestDB’
Alternatively, using DATABASEPROPERTYEX can also help to find the status of the database undergoing recovery.
SELECT DB_NAME() AS DatabaseName,
DATABASEPROPERTYEX(‘TestDB’, ‘Status’) AS DatabaseStatus
Here, DATABASEPROPERTYEX is the built-in metadata function in SQL Server that fetches the current state of a particular database. Besides, it also tells about various other properties or options for a selected database, such as recovery model, collation, edition, Updateability, and UserAccess.
Different States during SQL Database Recovery
Depending on the existing condition of the database, you will get any of the following states during a problematic recovery process:
RECOVERY_PENDING: Indicates the inability of the SQL Server to initiate the automatic recovery process required to ensure database consistency. It makes the recovery process stuck, instead of being a failed one. The reasons may include permission issues, disk space shortage, missing or deleted log files, etc.
RESTORING: This state signifies database restoration currently in process from a full, differential, or transaction log backup. This happens due to an incomplete RESTORE operation that misses the WITH Recovery command. You can expect this state during restores requiring multiple steps.
SUSPECT: When the recovery process starts but is unable to complete, it enters the SUSPECT state. It happens mostly because of a corrupted transaction log or file group, or due to a damaged file. The reasons may range from file corruption to OS crash, space limitation to hardware failure, and more.
EMERGENCY: This is an admin-imposed state via ALTER DATABASE, mostly used for an inaccessible or suspect database. It sets the database in single-user mode, allowing only read-only access required to recover or restore data. You need to be a member of the sysadmin fixed server role to set this state.
OFFLINE: The admin takes the database offline by using the ALTER DATABASE command to disable user access during crucial maintenance tasks, such as database recovery, upgrades, or file migration to a new storage disk. After the completion of the process, the admin brings the database back online.
What are the different types of errors occurring during database recovery?
Several states during the database recovery are a result of persistent errors that halt its functioning. Here are some of the errors that you may encounter during this process:
Database Stuck in RECOVERING or RESTORING state
Performing a database restore operation via the NORECOVERY option without using the final RESTORE WITH RECOVERY command leads to an incomplete process. Often, large transactions or those running for a long time consume considerable time in rolling the database back or forward. This also halts the database in the restoring or recovering state.
Error 9001: Unavailable database log
This error occurs when the database log files go offline due to a severe transaction failure in the database. Here is the message that you will encounter in the event of Error 9001:
Error: 9001, Severity: 21, State: 5.
The log for database ‘ContosoDb’ is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
However, the error does not provide information about the root cause, but its occurrence is common along with several other errors, like 9002, 3313, 3314, 17204, 17053, 823, etc. These error codes can provide better insight into the root cause.
Error 823: Severe system-level error
This error indicates an issue with the underlying storage system, driver, or the hardware that comes in the way of the I/O request. It occurs due to a damaged database or because of inconsistencies in the file system, which halts recovery operations. The error message contains information about various I/O operations:
- The database file against which you carried out the I/O operation
- The nature of the I/O operation, whether a Read or a Write request
- The file offset where the I/O operation took place. If you divide this offset number by 8,182, you will get the logical page number affected by the error.
Error 9002: The database log running out of space
Several reasons can account for a lack of space to accommodate database log, such as full disk volume, availability group synchronization, or incomplete database replication. It can also result from an uncleared log or from setting a maximum value for the log size. The error makes the transaction log full, blocking the entry of new transactions, which are inevitable for database recovery. As a result, it leaves the database in a Resource Pending, Recovery Pending, or Suspect state
Error 17053: Insufficient disk space
This generic operating system error results from a lack of proper disk space. As a result, it leads to a failed database snapshot creation, preventing the completion of DBCC CHECKDB and similar SQL Server database commands. The inability to access the database eventually results in a failed recovery process.
Error 17204: Database file inaccessible
If you cannot open a specific database or transaction log file, it is probably because of Error 17204. A database facing this error will acquire the Recovery Pending state, disabling the applications from accessing the database. It happens mainly due to the permission or file access issues and is common during the startup process, when the SQL Server opens the database files.
Error 3313: Database recovery in Suspect mode
The error happens when the database is unable to apply the committed transactions to the database files (redo). It prevents the database recovery operation during SQL Server startup, resulting in its inaccessibility. One prominent reason behind this error could be the storage of database or transaction log files in a failed or unavailable storage device.
Often, physically damaged files also cause write and read request issues, leading to the error. If left unresolved, it can damage the primary file group and other filegroups along with the transaction log file. To avoid the error, make sure that the storage devices of the database files are online.
SQL Server Database Recovery Errors: Troubleshooting Steps
Several steps can help to fix the issues and enable you to carry on with the database recovery without hassles:
Scan through the SQL Server error logs
The error logs contain crucial details about the failed database recovery. Going through them properly can help you know the real reason, and you can proceed with the right resolution accordingly.
Track Recovery Process
You can monitor the recovery process with the help of T-SQL queries. For example, the sys.databases system view or the DATABASEPROPERTYEX function can fetch information about the current state of the database. Moreover, to view recovery progress, you can use sys.dm_exec_requests, as shown in the query below:
SELECT * FROM sys.dm_exec_requests; OR
SELECT percent_complete, command, wait_type, wait_resource, last_wait_type
FROM sys.dm_exec_requests
WHERE command LIKE ‘%RECOVERY%’;
Check the database for consistency
To determine corruption issues in the database, you can run the DBCC CHECKDB command with various options. Let’s assume the database name is TestDB. You can enter your own database. Next, run the following basic command:
DBCC CHECKDB (‘TestDB’) with
- NO_INFOMSGS: To display error messages
- DATA_PURITY: To find out any invalid column values
- PHYSICAL_ONLY: To check the physical structure of the database, including the pages, header, etc.
In case the query detects corruption and you do not possess a healthy database backup, you may have to go with the REPAIR_ALLOW_DATA_LOSS as the last option. However, it deletes all the corrupted data, hence you need to be cautious before using it. Here is the command:
USE master;
ALTER DATABASE [‘TestDB’] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (‘TestDB’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [‘TestDB’] SET MULTI_USER;
GO
As you can infer from the queries, you need to set the database to single-user mode before performing the REPAIR_ALLOW_DATA_LOSS repair option.
Restore the database from a backup
A reliable solution to avoid database recovery errors is to opt for database restore from a recent and verified backup. As an added measure, enable Checksum during backups. This will avert issues arising with corrupted backup files. You can use the GUI of SQL Server Management Studio or any other IDE to create backups. Alternatively, T-SQL commands are also effective in creating a full, differential, or incremental database backup depending on your requirements.
Emergency mode
You can gain access to a database in the recovery pending state and fix it by putting it into Emergency mode. This will make the database read-only for the users, enabling the administrators to run diagnostics and rebuild the transaction log file. After setting the database to Emergency mode, your next step should be to set it to single-user mode. This will enable you to run DBCC CHECKDB with repair options. After resolving the corruption, reset the database to multi-user mode, as already discussed earlier.
Microsoft Fixes
Several issues related to database recovery can find easy resolution in the Microsoft fixes. To make the most of it, you must have an updated SQL Server instance.
Using professional SQL recovery software
If you want to get rid of the hassles involved with the manual methods to resolve SQL Server database recovery errors, you can use an automated alternative. Professional SQL recovery software, such as Stellar Repair for MS SQL or other similar tools, can work effectively in serving your cause. The software sets you free from undergoing the lengthy, time-consuming, and technically complex procedures. Moreover, these tools are quick to perform database recovery regardless of the current state of the database, whether mounted (online) or dismounted (offline).
Conclusion
Admins perform SQL Server database recovery to restore it to a consistent state after it becomes inaccessible due to a corrupted log file, hardware or software error, or any other reason. In several instances, the database experiences recovery errors during the process. This happens when it enters any of the states – recovery pending, suspect, or restoring.
Furthermore, the messages accompanying the error codes, such as Error 823, 3313, 9001, 9002, 17053, and 17204, also provide useful information. You can use these details to resolve the recovery issues and bring the database back to a working state by adopting the right fix. Several troubleshooting steps can prove effective in this context.
These may include checking the SQL Server error logs, monitoring the recovery process, or checking database consistency through DBCC CHECKDB before setting it in the Emergency mode. Besides, restoring the database from a recent backup is an extremely reliable restoration solution. Keeping SQL Server up-to-date can resolve the issue through Microsoft fixes.
These steps are effective in resolving the recovery errors, but do not guarantee a complete fix. In addition, you will end up spending significant time trying them, with increased downtime and loss of data among the other drawbacks. As an alternative, professional SQL recovery software such as Stellar Repair for MS SQL and others can help your cause significantly.
