Saturday, July 25, 2020

Database property

SQL Server Database Properties and Status

In SQL Server, each row in the system catalog represents a database within the instance. Understanding the status and properties of these databases is crucial for database administrators.

Database Status and Visibility

If a database is not ONLINE, or if the AUTO_CLOSE option is enabled and the database is closed, certain column values might return as NULL. When a database is in the OFFLINE state, users with lower privileges will not see the corresponding row. To access the row of an offline database, users must have at least the ALTER ANY DATABASE permission on the server or CREATE DATABASE permissions in the master database.

Retrieving Database Information

To retrieve detailed information about the databases, you can use the following SQL query:


SELECT database_id,
       CONVERT(VARCHAR(25), DB.name) AS dbName,
       CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
       state_desc,
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
       user_access_desc AS [User access],
       recovery_model_desc AS [Recovery model],
       CASE compatibility_level
         WHEN 60 THEN '60 (SQL Server 6.0)'
         WHEN 65 THEN '65 (SQL Server 6.5)'
         WHEN 70 THEN '70 (SQL Server 7.0)'
         WHEN 80 THEN '80 (SQL Server 2000)'
         WHEN 90 THEN '90 (SQL Server 2005)'
         WHEN 100 THEN '100 (SQL Server 2008)'
         WHEN 110 THEN '110 (SQL Server 2012)'
         WHEN 120 THEN '120 (SQL Server 2014)'
         WHEN 130 THEN '130 (SQL Server 2016)'
         WHEN 140 THEN '140 (SQL Server 2017)'
         WHEN 150 THEN '150 (SQL Server 2019)'
       END AS [compatibility level],
       CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
       ISNULL((SELECT TOP 1
                 CASE TYPE WHEN 'D' THEN 'Full'
                           WHEN 'I' THEN 'Differential'
                           WHEN 'L' THEN 'Transaction log' END + ' – ' +
                 LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
                 CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' +
                 CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' seconds)'
                 FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC), '-') AS [Last backup],
       CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
       CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
       page_verify_option_desc AS [page verify option],
       CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
       CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
       CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
       CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
       CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
       CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [clean shutdown]
FROM sys.databases DB
ORDER BY DB.name;

This SQL query retrieves various details about each database, including:

  • Database name
  • Status (e.g., ONLINE, OFFLINE)
  • Number of data and log files
  • Size of data and log files
  • User access
  • Recovery model
  • Last backup time
  • Other configuration options like auto-shrink, read-only status, etc.

Recovery Model

The recovery model of a database determines how transactions are logged and whether the database can be restored to a point in time. The common recovery models are:

  • FULL: Full recovery model
  • BULK_LOGGED: Bulk-logged recovery model
  • SIMPLE: Simple recovery model

Database Status

Here are some common database statuses:

  • ONLINE: The database is available for queries.
  • OFFLINE: The database is explicitly taken offline.
  • RESTORING: The database is in the process of being restored.
  • RECOVERING: The database is recovering and not yet ready for queries.
  • SUSPECT: The database is in a state that cannot be recovered.

For more detailed information on system catalog views, refer to the official Microsoft documentation:
Sys Databases View - Microsoft Documentation

Additional Resources:

at

Popular Posts