As a DBA, monitoring the status of all Databases or tracking the various parameters of the database is critical. In this article, we will be learning about the vital parameters of the SQL Server database as identified in sys.databases(DMV). Complete details about this DMV system catalogue view can be referenced from MSDN page. (Appendix 1)
Even though sys.databases DMV shows more than 75 columns, we are going to focus on the columns used most significantly across day-to-day activities.
As explained above, State_Desc column describes the current status of the database and below are the possible states a database can be in.
0 = ONLINE
Indicates that the current database is Online and available for users to access the database.
1 = RESTORING
Indicates that a user initiated the Backup Restore of this particular database and the Restoration process is not yet completed. If the database restore was initiated with RECOVERY option, then the database state will change from RESTORING to ONLINE once the backup restoration is completed. If the database restore was initiated with NORECOVERY option, then the database state will be in RESTORING till another Log backup or Differential backup or Full Backup file is done using RECOVERY option.
2 = RECOVERING
SQL Server uses the Recovery process to start the database in a transactionally consistent state and can happen out of the below 2 scenarios.
Database in RECOVERY status won’t be accessible for users. More details about RECOVERY process can be referenced in the MSDN page shown in Appendix 2.
3 = RECOVERY_PENDING
If the RECOVERY process is not completed due to common issues Disk Space constraints or disk unmounted from Servers or File Access permissions, then the RECOVERY process will fail ending up in RECOVERY_PENDING status. Databases in RECOVERY_PENDING status indicates that the database files are intact without any damages and hence can be recovered with some manual intervention.
Database in RECOVERY_PENDING status won’t be accessible for users.
More details about troubleshooting the RECOVERY_PENDING status will be discussed in the subsequent articles.
4 = SUSPECT
If the RECOVERY process is not completed due to corrupted database files, either Primary or Secondary Data files or Log files, then SQL Server will bring the database under SUSPECT mode. Potential reasons for SUSPECT mode may be Database corruptions, improper shutdown of SQL Server database or service while running a huge transaction, etc., Database in SUSPECT status won’t be accessible for users.
More details about troubleshooting database in SUSPECT status will be discussed in subsequent articles.
5 = EMERGENCY
To repair the SUSPECT databases from Corruption or to perform any Database maintenance, DBA can change the status of the database to EMERGENCY mode, which will place the database in Single user mode for further repairing or troubleshooting. Let’s say we have a corrupted database or table, then we need to take the database to EMERGENCY mode to have the database or table fixed out.
Database in EMERGENCY status will be accessible only to one user for troubleshooting or repairing purposes. Once the repair process is completed, the database can be changed to Multi_user access.
More details about troubleshooting database in EMERGENCY status will be discussed in subsequent articles.
6 = OFFLINE
Indicates that the database was taken OFFLINE by a DBA to perform some database maintenance. When the database is in OFFLINE mode, no users can connect to the database. Once the maintenance activity is completed, DBA can manually bring the database to ONLINE state.
7 = COPYING
10 = OFFLINE_SECONDARY
In addition to the above states, Azure SQL Databases can have 2 additional states like COPYING or OFFLINE_SECONDARY to achieve the Geo-Replication.
Note: For databases with Always On configured, database_state_desc column in sys.dm_hadr_database_replica_states will share similar information on the database states.
Log_reuse_wait_desc field is one of the most commonly selected field by a DBA to identify the reason why Transaction Log file keeps growing without reusing the existing file. Below are the possible values we can see for Log_reuse_wait_desc column.
0 = Nothing
Indicates the Transaction Log file is healthy, and SQL Server is reusing the existing Log file available.
1 = Checkpoint
Indicates that the Database engine is waiting for the next Checkpoint operation. SQL Server uses CHECKPOINT operation at regular intervals to ease the RECOVERY process by flushing all dirty pages to disk. Hence this is not a problem, and if required, we can manually run CHECKPOINT command to flush the dirty pages to disk.
2 = Log Backup
Indicates that the Database engine is waiting for Log backup to be taken to reuse the Log file. We can see Log Backup only if we have the Recovery model set to FULL or BULK Logged to ease in the Point in time database recovery. Taking a Log Backup manually will clear the Log Backup status to NOTHING.
3 = Active backup or restore
Indicates that an Active backup or restore process is happening and Database engine is waiting for the backup or restore operation to complete. Ideally, leaving the database to complete the Backup or Restore some time to let it complete can clear the Active Backup or Restore status to NOTHING.
4 = Active transaction
Indicates that the Database engine is waiting for the completion of an Active transaction that was running for a long time. Active or Open transactions prevent the reusing of Log files, and hence we need to identify the long-running transaction and either Commit or Rollback to get rid of this particular state.
5 = Database mirroring
Indicates that the Database engine is waiting for Database Mirroring Mirror node to catch up transactions from Principal node to reuse the Transaction log. Database Mirroring in Synchronous mode won’t have this issue as the records are synchronized immediately. Once the records transfer catches up, this status will get back to NOTHING or any other status applicable at that moment.
6 = Replication
Indicates that the Database engine is waiting for Replication Log Reader agent to push the records to Distributor. Similar scenarios can be noticed when CDC is enabled on the database. Once the records transfer catches up, this will get back to NOTHING or other status.
7 = Database snapshot creation
Indicates that the Database engine is currently waiting for the Database Snapshot creation to complete to keep the database in a transactionally consistent state.
8 = Log scan
Indicates that the Database engine is waiting for the Log Scan process to complete. Ideally, Log Scan operations can happen when we have Replication or CDC or Mirroring configured.
16 = XTP_CHECKPOINT
Similar to CHECKPOINT for a database with Memory-optimized Data filegroups
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.