SQL database has different recovery states such as Online, Offline, Restoring, Recovering, Recovery Pending, Suspect, and Emergency. SQL database is mostly in one specific state. State_desc command and DATABASEPROPERTYEX are easy ways to check the database state.
SELECT db_name () AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus
This is the output. In remaining section of this blog, I will discuss “SQL Database Recovery Pending” state, Offline state, and Emergency state of SQL database.
The SQL Server has faced resource-related error during the recovery process. Database is not damaged but files may be missing or limitation in system resources can be the cause which may be preventing database from starting. In this case, database needs an additional intervention by user or DBA to complete the recovery process.
How to fix: I created a database “Demo” to describe the solution. First, I ran DBCC CHECKDB command with NO_INFOMSGS
DBCC CHECKDB(Demo) WITH NO_INFOMSGS
My output was:
Msg 945, Level 14, State 2, Line 4
Database ‘Demo’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
In next step, I checked the error log of my database. To check the error log, click on SQL Server Agent->Error Logs. Click on Current error log. Select SQL Server and check its Current option. After clicking on it, a log file summary will be open. Apply filter to check information of the particular database.
After applying filters, I was able to detect the actual reason for this error. Error log message was:
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Log\Demo_log.ldf’. Diagnose and correct the operating system error, ad retry the operation.
I checked the log file of my database.
I renamed it as Demo_log and restart SQL Server. I fixed this issue successfully.
Read More: SQL database in recovery pending
In this state, database is unavailable and needs user action. Set database in the offline state when it is not required so as to make database accessible to other users. Users can create an SQL Server database in two ways, using SSMS and using T-SQL script. In SSMS, Right Click on database and go to Task option. Select Bring Online option from the list. Use the following T-SQL script to make database online:
Alter Database [database_name] set online
Only a member of SYSADMIN can set the SQL database in EMERGENCY state. The database remains in single user mode and is able to repair and restore. This state is in read-only mode and nothing can be written on the transaction log file. REPAIR_ALLOW_DATA_LOSS is the only option which works in EMERGENCY state. By using it, damaged data or indexes may be removed from the database to make database physical consistent.
EMERGENCY state is useful when SQL database in SUSPECT mode. A database goes into SUSPECT mode due to the following reasons:
Conclusion: These are few recovery states of SQL Server Database and their solutions. Sometimes, the database states related issues becomes very complex when user fails to recognize its reason. I hope these solutions will help you.
SELECT db_name () AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus
This is the output. In remaining section of this blog, I will discuss “SQL Database Recovery Pending” state, Offline state, and Emergency state of SQL database.
- SQL Server Recovery pending State
The SQL Server has faced resource-related error during the recovery process. Database is not damaged but files may be missing or limitation in system resources can be the cause which may be preventing database from starting. In this case, database needs an additional intervention by user or DBA to complete the recovery process.
How to fix: I created a database “Demo” to describe the solution. First, I ran DBCC CHECKDB command with NO_INFOMSGS
DBCC CHECKDB(Demo) WITH NO_INFOMSGS
My output was:
Msg 945, Level 14, State 2, Line 4
Database ‘Demo’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
In next step, I checked the error log of my database. To check the error log, click on SQL Server Agent->Error Logs. Click on Current error log. Select SQL Server and check its Current option. After clicking on it, a log file summary will be open. Apply filter to check information of the particular database.
After applying filters, I was able to detect the actual reason for this error. Error log message was:
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Log\Demo_log.ldf’. Diagnose and correct the operating system error, ad retry the operation.
I checked the log file of my database.
I renamed it as Demo_log and restart SQL Server. I fixed this issue successfully.
- SQL Database in Offline State
In this state, database is unavailable and needs user action. Set database in the offline state when it is not required so as to make database accessible to other users. Users can create an SQL Server database in two ways, using SSMS and using T-SQL script. In SSMS, Right Click on database and go to Task option. Select Bring Online option from the list. Use the following T-SQL script to make database online:
Alter Database [database_name] set online
- SQL Database in Emergency State
Only a member of SYSADMIN can set the SQL database in EMERGENCY state. The database remains in single user mode and is able to repair and restore. This state is in read-only mode and nothing can be written on the transaction log file. REPAIR_ALLOW_DATA_LOSS is the only option which works in EMERGENCY state. By using it, damaged data or indexes may be removed from the database to make database physical consistent.
EMERGENCY state is useful when SQL database in SUSPECT mode. A database goes into SUSPECT mode due to the following reasons:
- Hardware failure
- Damaged or corrupted log file.
- System failure
- Virus in SQL Server system
- Lack of disk space
- Improper shutdown
Conclusion: These are few recovery states of SQL Server Database and their solutions. Sometimes, the database states related issues becomes very complex when user fails to recognize its reason. I hope these solutions will help you.
