Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.
A SQL Server database state specifies the current running mode of that database. The database can be running in one state at a given time. The current state of a database can be verified by selecting the state_desc column of the sys.databases catalog view.
There are seven main states in which a SQL Server database can exit. The below SELECT statement will query the sys.databases catalog view for the name and state for all databases hosted in the current SQL Server instance:
|
1 2 3 |
SELECT name, state_desc FROM sys.databases |
The result, in my situation, will show that all databases hosted in the current SQL Server instance are operating in the ONLINE state as follows:
The current state of a specific SQL Server database can be viewed also by selecting the Status property from the DATABASEPROPERTYEX function. The below SELECT statement views the current status of the SQLShackDemo database using the DATABASEPROPERTYEX function:
|
1 2 3 4 |
SELECT DATABASEPROPERTYEX (N'SQLShackDemo', N'STATUS') AS N'Status'; GO |
Which is online as shown in the result below:
From the database availability side, the database can be fully available or fully unavailable. Between these two main states, a smooth transition should have occurred in optimal scenarios free of any problems that may interrupt that transition. In this article, we will describe the seven database states, the reasons for these SQL database states occurrences and how the database will act when operating in those states.
ONLINE
A SQL database that operates in an ONLINE state is available for end users access and functioning normally. In the ONLINE database state, the primary filegroup is online, although the critical database recovery process of the undo phase may still not have finished completely. The ONLINE state is the healthy state that the SQL database should move to smoothly after starting up the database.
From the Databases node of the SQL Server Management Studio, the database name e.g. SQLShackDemo with no special words between brackets, as shown below, indicates that the database is in ONLINE state
RESTORING
The RESTORING database state means that the user has initiated a database restore process, using RESTORE DATABASE or RESTORE LOG T-SQL command, in which one or more data files of the Primary filegroup is restored, or one or more secondary files are being restored in offline mode. In effect thish means that the database is not available for the end user access during the restoration process.
The default database restore option is the RECOVERY option, in which the database will be brought online back after completing the database backup restoration. Using the NORECOVERY restore option, that is used to restore multiple backup files, the database will be in the RESTORING state until it reaches to the last file in which the WITH RECOVERY option is used to bring the database online again after restoring the last backup file.
The below RESTORE DATABASE command using the NORECOVERY restore option will keep the database in RESTORING state:
|
1 2 3 4 5 6 7 |
USE [master] RESTORE DATABASE [SQLShackDemo] FROM DISK = N'D:\backupSQL\SQLShackDemo_2016-08-19-001218.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 , NORECOVERY GO |
The same process can be performed using the Restore Database window of the SQL Server Management Studio. The database restore option can be specified from the Options tab of that window as follows:
Performing the previous restoration process, the database will be in the RESTORING state, with the special word “Restoring” between brackets beside the database name, as shown below, indicating that the database is in RESTORING state:
If the database still operating in the RESTORING state and there is no backup file to be restored to the database, you can recover the database and bring it ONLINE simply by applying the below RESTORE DATABASE …. WITH RECOVERY command:
|
1 2 3 4 5 6 |
USE [master] RESTORE DATABASE [SQLShackDemo] WITH RECOVERY GO |
The database will be brought online again by restoring no new page as follows:
RECOVERING
The Database RECOVERING state is a transient state, in which the database is performing a recovery process and will become ONLINE automatically, if the recovery process completed successfully, after the database startup.
The recovery process consists of two main phases, the Roll Forward phase, in which any transaction that is committed while shutting the database down and not yet written to the database data files will be processed. In the Rollback phase, any transaction that is uncommitted during the database shutting down will be rolled back. If the recovery process failed for any reason, the database will be moved to the SUSPECT state and will become unavailable. Working in RECOVERING state, the database will be unavailable for the users.
The special word “In Recovery”, between brackets beside the database name indicates, as shown below, that the database is in RECOVERING state:
The RECOVERING database state is a transient state as we mentioned previously, that is performed on the database startup or after restoring the last backup file. In normal cases, the database will not be in the RECOVERING state for long time. One of the most common issues that leads the database to stay in RECOVERING state, for a longer time and slow down the recovery process, is an excessive number of Virtual Log Files (VLFs), up to tens thousands of them, inside the database transaction log.
The number of VLFs can be verified by running the below DBCC command on your database:
|
1 2 3 4 5 |
USE SQLShackDemo GO DBCC LOGINFO |
The command in our case will return 86 records as follows:
This excessive number of VLFs is mainly generated due to growing the database transaction log very frequently and in very small increments. To overcome that issue, you should perform a transaction log backup on your database, shrink the transaction log as much as possible and finally specify an initial size of the transaction log file large enough to handle the database workload, without the need for frequent growth. Checking the number of VLFs again after performing the transaction log backup and the shrink operations:
|
1 2 3 4 5 |
USE SQLShackDemo GO DBCC LOGINFO |
The number will be decreased to 35 as in the result below:
RECOVERY PENDING
Having your database stuck in RECOVERY PENDING state, means that the database recovery process failed, due to missing files or potentially for resource-related reasons, preventing the database from being recovered successfully, but that the database is not damaged. In this case, the database will be unavailable for the user access and required a further user action to resolve the error and let the recovery process be completed successfully.
The special word “Recovery Pending” between brackets beside the database name, as shown, indicates that the database is in a RECOVERY PENDING state:
The SQL Server error log is the best place from which you can start your investigation. In our case, the error log shows that the database has not recovered successfully due to a database missing file that may be deleted or renamed:
Locating the missing file again, taking the database offline and brining it online, the database will be recovered completely as shown in the error log event below:
SUSPECT
A database that is in the SUSPECT states means that the database is not available for user access. In this database state, the database recovery process has started but not completed successfully, requiring further user action in order to fix that issue and repair the corrupted files. SQL Server marks a database as SUSPECT due to many reasons, such as database files corruption, unavailable database files or improper shutdown of the SQL database server while running a big transaction.
Let us simulate a database corruption situation in which the SQL Server will mark the database as SUSPECT. We will create a new testing database, create a simple table in that database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [master] GO CREATE DATABASE [SuspectDBDemo] GO USE [SuspectDBDemo] GO CREATE TABLE [Employees] ( [ID] INT, [FirstName] VARCHAR (50), [LastName] VARCHAR (50), [Address] NVARCHAR (MAX)); GO |
Moreover, we will fill that table with 1000 records using the ApexSQL Generate, synthetic test data tool as follows:
What we will do is start a transaction that will update the testing table without committing it, and perform a CHECKPOINT command to write it to the disk.
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN UPDATE [Employees] SET [Address] = 'AMM' WHERE [LastName] like 'Evans'; GO CHECKPOINT GO |
At the same time from another session, we will perform a SHUTDOWN command to terminate the SQL Server process:
|
1 2 3 4 |
SHUTDOWN WITH NOWAIT; GO |
After that, we will open the database log file using any hex editor, and modify the first section by filling it with zeros and save the file again as below:
Finally, we will start the SQL Server service again. If you try to run any simple query on that database, an error showing that the database is inaccessible will be displayed as follows:
Checking the database state using the DATABASEPROPERTYEX function:
|
1 2 3 |
SELECT DATABASEPROPERTYEX ('SuspectDBDemo', 'STATUS') AS DatabaseStatus |
The result will show that the database is in SUSPECT state:
The special word, “Suspect” between brackets beside the database name, as shown below, indicates that the database is in SUSPECT state:
Again, and always, referring to the SQL Server error log will help you finding the root cause of the database SUSPECT issue, which is an issue with the log file in our case as follows:
EMERGENCY
The database can be changed to the EMERGENCY state by a sysadmin user action, in order to safely perform database maintenance or for troubleshooting purposes. In this state, the database will be in single-user mode to be repaired or restored, marked as READ_ONLY where you can export the data out of the database, logging is disabled and the access is restricted only to the sysadmin role members.
Let us get back again to the previous SuspectDBDemo corrupted database that is marked as SUSPECT. In order to troubleshoot its problem and resolve it, we will change the database state to EMERGENCY, permitting the sysadmin users read-only access to that database. The below ALTER DATABASE statement is used to set the database state to EMERGENCY:
|
1 2 3 4 |
ALTER DATABASE SuspectDBDemo SET EMERGENCY GO |
The special word “Emergency” between brackets beside the database name, as shown below, indicates that the database is in EMERGENCY state:
Having the database in EMERGENCY state, we can work on resolving the problem safely. In order to check for database corruption, a DBCC CHECKDB command can be executed while the database in EMERGENCY state. Before doing that, the database should be changed explicitly to run using SINGLE_USER mode using the below ALTER DATABASE command:
|
1 2 3 4 |
ALTER DATABASE SuspectDBDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO |
The DBCC CHECKDB command can be executed now on that database, with the REPAIR_ALLOW_DATA_LOSS option. Damaged data and/or indexes may be removed to make the database physically consistent, but with possible data loss. In addition, the transaction log file will be rebuilt if there is any problem with that transaction log. The below DBCC CHECKDB command is used to fix the database corruption:
|
1 2 3 4 |
DBCC CHECKDB (SuspectDBDemo, REPAIR_ALLOW_DATA_LOSS) GO |
The clear DBCC CHECKDB command result in our case will be as follows:
Changing the database running mode back to MULTI_USER mode:
|
1 2 3 4 |
ALTER DATABASE SuspectDBDemo SET MULTI_USER WITH ROLLBACK IMMEDIATE GO |
The database transaction log file corruption issue is resolved now, after rebuilding the log file, and the database is ONLINE again and available for the user access:
If you run a simple SELECT statement from the database table, the data will be retrieved with no issue:
OFFLINE
When the database is in OFFLINE state, the database is not functioning nor unavailable for the user access. The database state can be changed to or from OFFLINE state only by an explicit user action. Setting the database state to OFFLINE helps in migrating the database files to a new disk drive, or preventing the users from reaching it for any reason. The below ALTER DATABASE statement is used to change the database state to OFFLINE:
|
1 2 3 4 |
ALTER DATABASE [MDW] SET OFFLINE GO |
The same action can be performed using the SQL Server Management Studio, right-clicking on the database -> Tasks and choose Take Offline task as follows:
The red arrow and the Offline special word between brackets beside the database name indicates that the database is in OFFLINE state as follows:
Taking into consideration that the database will stay offline unless you perform an explicit action to bring it online. The below ALTER DATABASE statement will bring the database online back:
|
1 2 3 4 |
ALTER DATABASE [MDW] SET ONLINE GO |
Bringing the database online using the SQL Server Management Studio can be performed by, right-clicking on the database -> Tasks and choose Bring Online task:
Conclusion
In this article, we’ve described seven different states of a SQL Server database, showed how a database operated in these states and how to move the database from one state to another.
Changing the database state is critical and you should make sure that this change is performed at the right time, in a correct situation, and that you have the recovery plan in the case of failure.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021