How repair an ASM database in a “SUSPECT” or “RECOVERY PENDING” status?

KB51307

Ultimo aggiornamento: 22 May 2012

ASM database recovery

3.2.5

This article may be applied to ASM versions using a SQL Server 2005/2008 database.

Introduction

There can be a possibility that when you connect to an SQL Server Instance you will find the ASM ("ASM_DB" by default) database being marked as "SUSPECT" or "RECOVERY PENDING".

During such scenarios, ASM will not be able to connect to the database, getting errors into its logs and into the Web interface.

In this article we will go through the steps which you need to follow to recovery a database which is marked as "SUSPECT" or or "RECOVERY PENDING".

"RECOVERY PENDING" may occurs if SQL Server has encountered a resource-related error during recovery.

Some of the reasons why an SQL Server database can be marked as "SUSPECT"

1. Database could have been corrupted.
2. There is not enough space available for the SQL Server to recover the database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
4. Database files are being held by operating system, third party backup software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.


Recovery from a "RECOVERY PENDING" status

A simple restart of the SQL Service should be enough to get the ASM database online again.

Steps to recover a database marked as "SUSPECT"

These steps require to run TSQL code. You can run it by the SQL Server Management Studio Query executor for example.

0. You can check why the database gone to "SUSPECT" status opening the latest SQL Server Error Log and checking for errors logged for the ASM_DB database. You can open SQL Server Error Log by expanding Management Node of SQL Server Error Logs.

Sample Error Messages within SQL Server Error Log when database is marked as "SUSPECT":

Starting up database 'ASM_DB'.
Error: 9003, Severity: 20, State: 9.

The log scan number (189624:16:2) passed to log scan in database 'ASM_DB' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Error: 3414, Severity: 21, State: 1.

An error occurred during recovery, preventing the database 'ASM_DB' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

CHECKDB for database 'ASM_DB' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.


1. Select the ASM database

USE ASM_DB
GO

2. When a database is in "SUSPECT" mode you will not be able to get connected to the database. Hence you need to bring the database first in "EMERGENCY" mode to repair the database. Execute the below mentioned TSQL code to bring the database in "EMERGENCY" mode.

ALTER DATABASE ASM_DB SET EMERGENCY
GO

Once the database is in "EMERGENCY" mode you will be able to query the database.

3. Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (ASM_DB)
GO

4. Next step will be to bring the user database in "SINGLE_USER" mode by executing the below mentioned TSQL code.

ALTER DATABASE ASM_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

5. Once the database is in "SINGLE_USER" mode execute the below TSQL code to repair the database. When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.

DBCC CHECKDB (ASM_DB, REPAIR_ALLOW_DATA_LOSS)
GO

6. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE ASM_DB SET MULTI_USER
GO

We also recommend to disable the SQL Server Auto Close function for the ASM database.