Database States in SQL Server & Recommended Actions

SQL Server databases can be in different states based on their availability and health. Below is a detailed explanation of each state, the causes, and the actions needed to resolve issues.

In SQL Server, a database can be in various states, each indicating a specific condition or phase of operation. Understanding these states is crucial for database administrators (DBAs) to manage and troubleshoot databases effectively. Below is an explanation of each database state and the actions you might need to take:

1. ONLINE

• Description: The database is available for access. All operations can be performed on the database.
• Action: No action is required. This is the normal state for a database in use.

2. OFFLINE

• Description: The database has been manually taken offline and is not accessible.
• Action: To bring the database back online, use the following command:

ALTER DATABASE [DatabaseName] SET ONLINE;

• Considerations: Ensure that the database files are accessible and not corrupted before bringing it online.

3. RESTORING

• Possible Causes: A restore operation is in progress. The database is not accessible during this state.

o    A database restore operation is in progress.

• The database is being recovered from a backup.
• Action: Wait for the restore operation to complete. If the restore is taking too long or appears stuck, check the SQL Server error log and the restore process for any issues. If you are performing log shipping, apply further logs before recovering.

RESTORE DATABASE [DB_Name] WITH RECOVERY;

• Considerations: Ensure that you have valid backups and that the restore process is correctly configured.

4. RECOVERING

·  Possible Causes:

·         Automatic recovery is in progress. This state occurs when SQL Server is rolling forward committed transactions and rolling back uncommitted transactions during startup.

·         SQL Server restarted after an unexpected shutdown.

·         Crash recovery process is running.

·  Actions to Fix:

·         Wait for SQL Server to complete the recovery.

·         If the database remains in this state for an extended period, investigate potential issues such as long-running transactions or hardware problems.

·         If stuck, check the SQL Server error log:

EXEC sp_readerrorlog;

·         Restart the SQL Server service if needed.

• Considerations: Monitor the SQL Server error log for any recovery-related messages.

5. RECOVERY_PENDING

• Description: Recovery is required but has not started. This could be due to issues such as missing or corrupted transaction log files.

·         Possible Causes:

·         Missing or corrupt transaction log file.

·         Disk space issues preventing recovery.

·         Forced shutdown during recovery.

• Action: Investigate the cause of the recovery pending state. Check the SQL Server error log for details. You may need to restore from a backup or repair the database.

ALTER DATABASE [DatabaseName] SET ONLINE;

·         Actions to Fix:

1.      Try bringing the database online:

ALTER DATABASE [DB_Name] SET ONLINE;

 

2.      Run the following command to check for corruption:

DBCC CHECKDB([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

 

3.      If logs are missing or corrupt, try forcing recovery:

ALTER DATABASE [DB_Name] SET EMERGENCY;

DBCC CHECKDB([DB_Name], REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE [DB_Name] SET ONLINE;

 

4.         If the log file is missing and database is stuck, consider restoring from backup.

·         Considerations: Ensure that all necessary database files are available and not corrupted.

6. SUSPECT

·         Description: The database is marked as suspect due to corruption or missing files. The database is not accessible.

·         Possible Causes:

·         Corrupt MDF/LDF files.

·         Disk failures.

·         SQL Server crash during a transaction.

·         Action:

1.    Check the SQL Server error log for details on why the database is marked as suspect.

2.    Attempt to bring the database online using:

--Set database to emergency mode:

ALTER DATABASE [DB_Name] SET EMERGENCY;

--Check for errors:

DBCC CHECKDB([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

--Attempt to repair:

DBCC CHECKDB([DB_Name], REPAIR_ALLOW_DATA_LOSS);

--Bring it back online:

ALTER DATABASE [DB_Name] SET ONLINE;

--If repair fails, restore from a backup.

 

3.    If the database cannot be brought online, you may need to restore from a backup or use DBCC CHECKDB to repair the database.

Considerations: Use DBCC CHECKDB to identify and repair corruption. Always have a valid backup before attempting repairs.

7. EMERGENCY

• Description: The database is in a read-only state for troubleshooting purposes. Only members of the sysadmin fixed server role can access the database.
• Action: Use this state to perform diagnostics and repairs. You can run DBCC CHECKDB with repair options to fix issues.

ALTER DATABASE [DatabaseName] SET EMERGENCY;

DBCC CHECKDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE [DatabaseName] SET ONLINE;

• Considerations: Be cautious with repair options that allow data loss. Always attempt to restore from a backup first if possible.

·  Possible Causes:

• DBA manually sets it for troubleshooting.
• Database corruption.

·  Actions to Fix:

• If needed for repair, run:

DBCC CHECKDB([DB_Name], REPAIR_ALLOW_DATA_LOSS);

• Restore from a backup if necessary.
• Bring the database back online:

ALTER DATABASE [DB_Name] SET ONLINE;

Summary of Actions:

• ONLINE: No action needed.
• OFFLINE: Bring the database online using ALTER DATABASE [DatabaseName] SET ONLINE;.
• RESTORING: Wait for the restore to complete or troubleshoot if necessary.

Understanding these states and the appropriate actions to take can help ensure the availability and integrity of your SQL Server databases.

Quick Troubleshooting Summary

State	Possible Causes	Action Needed
ONLINE	Normal operation	No action needed
OFFLINE	DBA manually took it offline	ALTER DATABASE [DB_Name] SET ONLINE;
RESTORING	Restore in progress	Wait for the restore to complete or troubleshoot if necessary. RESTORE DATABASE [DB_Name] WITH RECOVERY;
RECOVERING	Automatic recovery running	Wait or check logs
RECOVERY_PENDING	Recovery required but cannot start	Investigate and resolve the issue preventing recovery. Check corruption, repair logs, restore backup
SUSPECT	Corruption, missing files, or crash	Diagnose and Emergency mode, repair, restore backup
EMERGENCY	Set manually for troubleshooting	Repair corruption, restore, bring online