SQL Server database possible statuses are as follows:
- EMERGENCY: A user has changed the database and set this status. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
- SUSPECT: At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during the SQL Server startup and is unavailable. The user must take additional action to resolve the problem.
- OFFLINE: The database is unavailable. It becomes offline by explicit user action and remains offline until another user action is taken.
- RESTORING: One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
- RECOVERING: The database is being recovered from a backup. The recovery process is transient; if it succeeds, the database will automatically become online. If it fails, the database will become suspect. The database is unavailable.
- RECOVERY PENDING: SQL Server encountered a resource-related error during recovery; therefore, it’s in an intermediate state of recovery. The database is not damaged, but files may be missing, or system resource limitations may prevent it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
It’s important to take action once the database status is not online and you are aware that the database should be accessible for operations.
Symptoms
The status of the SQL database is not ONLINE
Impact: Critical
The database is unavailable for SQL operations.
Expected behavior
Any instance where the status is not ONLINE needs to be investigated.
Find out how you can save hours of work when investigating the root cause of this issue.
Possible causes
1- Database corruption. Priority: Critical
When the SQL Server starts up, it will try to run recovery on any corrupted database. Where this fails, it will put the database into the SUSPECT state.
It’s important to recognize this issue early since the impact is meaningful for performance.
Problem identification:
Recognize databases that their status has been changed to SUSPECT.
- Run regular database integrity checks in order to detect database corruption.
- Look for error messages related to corruption in SQL Server’s error logs, Windows Event Viewer(for Windows OS),’ journalctl’ (For Linux), and SQL Server’s ERRORLOG file.
- Track errors using extended events or tracking tools such as SQL Server Profiler. When a database is corrupted, users that work with it would probably face errors during performing operations.
- Evaluate your current backup plan. It should be modified according to the needs of which point of time needed to recover, importance, and available resources.
- Track queries to prevent future occurrences.
Recommended action :
The best option is to restore the database from backups with the help of an expert DBA. If no backups are available, try to get the database into EMERGENCY mode and extract as much data as possible, or run EMERGENCY-mode repair.
You should focus on future occurrences and try to prevent this issue from happening again by tracking it with a monitoring tool.
2- Database recovering or restoring. Priority: High
In an SQL Server, the restoration procedure of a database means copying the backup of the database file and moving it to the recovery point. The database recovery involves rolling forward uncommitted transactions and making the database online. Once the processes are completed, this status needs to change to online.
Problem identification:
Recognize databases whose status has been changed to recovering, restoring, or recovery pending. You are able to do it by tracking the queries involving these commands.
- Identify the RECOVERING state by using the SSMS (SQL Server Management Studio) and connecting the correct SQL instance. Navigate to “Object Explorer” and look for databases’ names with a small circular arrow icon. Otherwise, write an SQL query that uses the system views to identify which databases are in the RECOVERING status.
- Identify the RESTORING state by using the SSMS and navigating to databases’ names there would be a backup icon. Otherwise, execute SQL query which uses the system views in order to identify which databases are in RESTORING status.
- Identifying the RECOVERY PENDING status of databases quickly is more critical since it means that SQL Server encountered errors while restoring the database. Identify it by using SSMS, navigating to databases’ names where there is an exclamation mark icon. Otherwise, execute an SQL query that uses the system views to identify which databases are in RECOVERY PENDING status.
- Another option is looking for queries involving commands of restoring or recovering. To do this, you should use tracking tools such as SQL Server Profiler. This step will provide more details that would be helpful for troubleshooting if needed.
- For the RECOVERY PENDING status, you should also examine SQL Server error logs. The error logs might provide information about missing files, corruption, or other issues.
Recommended action :
Monitor the recovery or restore process to ensure that it is completed. If the processes are stuck, check the database files accessible and the disk’s available space, and check for possible database corruption by running DBCC checks.
For RECOVERY PENDING status, you will probably need to restore the database from backup after dealing with the causes of errors.
3-User action. Priority: Low
When database status changes following a user’s action, it’s important to understand its reason, whether it’s a test database or if it’s done because of a severe issue.
Problem identification:
Identify the incidence of database status change and whether a specific user initiated it.
- Once you identify the time the database status was changed, check the SQL Server Error Logs and look at events around the same time period. You should find information about what action was taken and which user or application initiated it.
- Check if auditing is enabled for your database and review the audit logs.
- Examine the transaction log to identify recent transactions to the database in which status is changed. This will provide you with exact information about what occurred during the specific period.
- If the status change was triggered by a specific query, you might be able to find it in the query execution history or query cache.
- Review the event viewer or application logs which might provide data about the user’s actions.
Recommended action :
Based on the specific need of the user who initiated the change and the database’s importance, you should decide whether the change policy needs to be reevaluated and whether further actions are required following the change.
If the database status was changed unintendedly, you should consider conducting a security review to prevent unplanned changes like that in the future.
