“ I installed SQL Server 2008 and its instance name is different. I restarted my SQL Server and after restarting my system I found that some of my databases are in recovery pending state.Why is this happening? Please suggest me solution to fix this recovery pending error.”
This might have happen to you also when database shows you state except Online, which is a problem. Database becomes inacessible encountering “Pending State Error”.
Confused? In which State you are in
If you are confused in what state your database is, you can simply check the state of a database by sys.databases catalog view or by DATABASEPROPERTYEX function.
If your database is in recovery pending state, the DatabasePropertyex function will show database in Suspect Mode.
Why is this happening ??
There are various reasons for getting this error, some of them are listed below:
- Corruption in Master Database File
- Power Failure
- Faulty Hardware
- Sudden shutdown of SQL Server
- Less memory space or insufficient disk space
- Database is not cleanly shut down
- Files which are used by the database dont belong to same database.
Error You May Encounter
While trying to bring database in Online Mode, user may encounter the following errors:
"Error: 5173, Severity: 16, State: 1. One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from backup."
"Database [dbname] cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."
"Log file [log name] does not match the primary file. It may be from a different database or the log may have been rebuilt previously."
Fix Database Recovery Pending State SQL Server
Note : Take backup of Primary (.mdf), secondary (.ndf) and log (.ldf) database files
Perform step by step procedure to resolve Recovery Pending State error.
- Set database to Emergency Mode:
- Set database in Multi User.
- Now Detach and Reattach the data file by running below commands:
ALTER DATABASE [dbname] SET EMERGENCY
ALTER DATABASE [dbname] SET MULTI_USER
EXEC sp_detach_db '[dbname]'
EXEC sp_attach_single_file_db @dbname = '[dbname]',
@physname = N'[mdf path]'
Or if your database file is corrupted, perform the following steps:
ALTER Database [DBName] SET EMERGENCY;
ENTER
ALTER Database [DBName] SET Single_user ;
ENTER
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS ;
ENTER
This will repair the database corruption, if persists in SQL Server Database.
ALTER Database [DBName] SET Multi_user;
ENTER
Making Smart Move
There could be the possibility that the Pending state does not get resolved by the manual approach. For better and quick approach to resolve SQL Server Recovery Pending State is using MDF Data Recovery. It repair your database from Suspect Mode and make your database accessible for use. Also if your database file is found to be corrupted, the software recovers the database from Corruption.
Conclusion
Database not accessible ? You might be facing Recovery Pending State error. The blog discusses the same. It covers the reasons why recovery pending state error occurs and the solution for the same.
EmoticonEmoticon