SQL Server Database Health Checks

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
I'm a SQL Server Architect experienced in Design, Development, Administration, Performance Tuning.. Exploring BI, SSIS & Data Science Arena!
Published:
Updated:
An introduction article to discuss about the various Database states for troubleshooting or health check purposes. More articles will be available in this series to see how to troubleshoot each and every states described in this article.

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.


  1. Name – Name of the database
  2. Database_id – ID of the database
  3. Owner_sid – Owner of the database
  4. Create_date – Date when the database was created or last renamed out.
  5. Compatibility_level – Compatibility level of the database
  6. Collation_name – Collation of the database
  7. User_access_desc – Describes whether the database can be accessed by Multiple users or Single User or Restricted users only.
  8. Is_read_only – Describes whether database is read-only or not.
  9. Is_auto_close_on – Describes the status of AUTO_CLOSE property.
  10. Is_auto_shrink_on – Describes the status of AUTO_SHRINK property.
  11. State_desc – Describes the state of the database. Very useful to identify the current state of the database and will help in restoring a corrupt database or inaccessible database. See below for more insight.
  12. Is_in_standby – Describes whether the database is in Standby mode or not.
  13. Recovery_model_desc – Describes the Recovery model state as either FULL or BULK_LOGGED or SIMPLE.
  14. Is_auto_update_stats_on – Describes the property of AUTO_UPDATE_STATISTICS
  15. Is_auto_update_stats_async_on – Describes the property of AUTO_UPDATE_STATISTICS_ASYNC
  16. Lot of ANSI options – Describes whether those ANSI settings are turned ON or OFF.
  17. Is_published – Describes whether the database is Published as part of Replication.
  18. Log_Reuse_wait_desc – Describes the Transaction Log Reuse state of the database. One of the most commonly used fields for troubleshooting database status. See below for more insight.


Database State_Desc

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.


  • Starting an instance of SQL Server will initiate the RECOVERY process for all databases starting with master, resourcedb, model, tempdb and all user databases.
  • Bringing online a specific database which was earlier in OFFLINE status.

RECOVERY process involves 3 phases with respect to the last Database Checkpoint status. internally.

  1. ANALYSIS phase – analyses the Transaction log to identify the last Checkpoint and created the Active Transaction Table (ATT) to capture the Active transactions at the time of last Checkpoint and Dirty Page Tables (DPT) to capture the dirty records at the time database was shut down.
  2. REDO phase – identifies the Minimum Log Sequence Number (LSN) from the DPT records and writes the dirty pages related to Committed transactions to the data file.
  3. UNDO phase – identifies all Active transactions that were not committed due to database shutdown and rolls back all these records to maintain the Database Integrity.


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.


Database Log_reuse_wait_desc

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

 

Appendix

  1. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15
  2. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver15


0
136 Views
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
I'm a SQL Server Architect experienced in Design, Development, Administration, Performance Tuning.. Exploring BI, SSIS & Data Science Arena!

Comments (0)

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.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community