Background

Error 17197 occurs when an Azure SQL Database is in the Recovery Pending state. This means the database cannot start the recovery process because it is waiting for necessary resources or there’s a problem accessing the files required for recovery. This state typically occurs after an unexpected shutdown or system crash. The recovery process is needed to bring the database back to a consistent state, but it cannot proceed due to issues such as corrupted files, resource limitations, or disk access problems.

Summary Table

Error Explanation

The Recovery Pending state indicates that SQL Server knows recovery is needed but cannot start the recovery process. This state is different from “Suspect” or “Offline” states, as it implies that the system is waiting to access resources or that some operations need to complete before recovery can proceed. SQL Server will not allow you to access the database while it’s in this state.

The error message might look like this:

Error 17197: The database is in recovery pending state and cannot be accessed.

Common Causes

1. Sudden Power Loss or System Crash: The database was not shut down cleanly, leading to the pending recovery.
2. File Corruption: The database files (data or log files) are corrupted and cannot be accessed properly.
3. Resource Exhaustion: Lack of necessary resources such as memory, CPU, or disk space required for the recovery process.
4. Transaction Log Issues: The transaction log is incomplete or corrupted, preventing recovery from being completed.
5. Storage Access Problems: The server cannot access the storage where the database files are located.

Steps to Troubleshoot and Resolve Error Code 17197

1. Check the State of the Database

To verify that the database is in the Recovery Pending state, use the following query:

SELECT name, state_desc 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

If the result shows RECOVERY_PENDING, you’ll know that the database is stuck in this state and recovery cannot begin.

Example:

name               state_desc
YourDatabaseName   RECOVERY_PENDING

2. Check SQL Server Error Logs for More Information

The SQL Server error logs will provide detailed information about why the database is in the Recovery Pending state. You can access the logs to find out if there’s a specific issue with database files, log files, or resource availability.

Query to Read SQL Error Logs:

EXEC xp_readerrorlog 0, 1, N'YourDatabaseName';

Look for error messages that reference issues like file corruption, transaction log problems, or memory/disk shortages. This will help pinpoint the root cause.

3. Check Resource Availability (Memory, Disk Space, CPU)

The Recovery Pending state could be due to a lack of resources needed to perform the recovery process. You need to check if the server has enough available resources such as memory, CPU, or disk space.

• Disk Space: Ensure that there is sufficient disk space available for recovery to complete.
• Memory: Verify that the server has enough memory to load the database during recovery.
• CPU Usage: Check if the server is under heavy CPU load, which may delay the recovery process.

You can check these resource metrics in the Azure Portal under the Monitoring section for your SQL Database.

Steps to Check Resource Usage:

1. Go to Azure Portal.
2. Select your SQL Database.
3. Under Monitoring, select Metrics.
4. View metrics like DTU usage (for DTU-based models) or vCore usage (for vCore-based models) and Disk I/O.

4. Run DBCC CHECKDB to Check for Corruption

If the database files are corrupted, SQL Server may be unable to start the recovery process. Running the DBCC CHECKDB command can help identify and possibly repair corruption in the database.

Run the following command:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

This will check the integrity of the database, including the transaction logs, and report any errors or corruption. If corruption is detected, you may need to take further steps to repair it.

5. Attempt to Repair the Database

If the DBCC CHECKDB command identifies corruption, you may need to run the command with repair options.

Steps to Repair the Database:

1. Take a full backup of the database before attempting any repairs.
2. Run the following command to repair any minor issues without data loss:

DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD);

This command will attempt to rebuild the database index structures and repair minor corruption.

• If more serious corruption is detected, you may need to use:

DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

Note: Using REPAIR_ALLOW_DATA_LOSS can result in some data loss, as it removes corrupted data to bring the database back online.

6. Restart SQL Services or the Database

In some cases, a simple restart of the SQL Database service or even the entire Azure SQL Server might clear the Recovery Pending state. While you cannot restart the Azure SQL Database service directly, you can force a restart by scaling the database tier up and then back down.

Steps to Restart via Scaling:

1. Go to Azure Portal.
2. Select your SQL Database.
3. Click on Pricing tier under the Settings menu.
4. Scale up to a higher tier and click Apply.
5. After the process completes, scale back down to the original tier.

This forces the database to restart, which may resolve temporary resource issues.

7. Restore from Backup

If all other attempts to recover the database fail, and the database is still stuck in the Recovery Pending state, the last option may be to restore the database from a recent backup.

Steps to Restore the Database:

1. Go to Azure Portal.
2. Navigate to your SQL Database.
3. Select Backups from the menu.
4. Choose the most recent backup that was completed successfully and click Restore.

This will revert the database to the point when the backup was taken, potentially bypassing the corruption or resource issue.

Workarounds

• Free Up Resources: Ensure that enough memory, disk space, and CPU are available to complete the recovery process.
• Monitor Resource Usage: Regularly monitor resource consumption in Azure Portal to ensure that you don’t run into resource exhaustion issues.
• Restart the Database: Scale the database up and down to force a restart, which might clear temporary issues.

Solutions

1. Run DBCC CHECKDB: Use this command to check for and repair corruption in the database files.
2. Check Resource Availability: Ensure that there is enough memory, CPU, and disk space to complete the recovery process.
3. Restart the Database: Force a restart by scaling the database tier up and down.
4. Restore from Backup: If the issue persists, restore the database from a recent backup.

Example Scenario

Suppose you’re running a business intelligence application, and one morning, users report that they cannot access the SalesDB database. You try to connect to the database, but receive the following error:

Error 17197: The database is in recovery pending state and cannot be accessed.

Step 1: You check the database state using the query:

SELECT name, state_desc 
FROM sys.databases 
WHERE name = 'SalesDB';

The result shows:

name         state_desc
SalesDB      RECOVERY_PENDING

Step 2: You run the following command to check the SQL Server error logs:

EXEC xp_readerrorlog 0, 1, N'SalesDB';

The log indicates that there is a disk space issue preventing recovery from completing.

Step 3: You check the Azure Portal and find that the disk space is almost full. You free up space by deleting unused files or expanding the disk.

Step 4: Once disk space is freed, you force the database to restart by scaling the SalesDB database from Standard S2 to Premium P1 and then back.

Step 5: After the restart, the database is no longer in the Recovery Pending state, and users can access it normally.

By following these steps, you can diagnose and resolve Error 17197 in Azure SQL Database, getting your database out of the Recovery Pending state and back online.