SQL Server DBA日常检查常用SQL

下面我将为你详细讲解SQL Server DBA日常检查常用SQL的完整攻略。

一、日常检查SQL

作为SQL Server DBA，需要定期对数据库进行日常检查，以便保证系统的稳定性、安全性和可靠性。以下是常用的日常检查SQL：

1. 检查数据库状态

SELECT DB_NAME(database_id) AS [Database Name],
       CASE
         WHEN state = 0 THEN 'Offline'
         WHEN state = 1 THEN 'Online'
         WHEN state = 2 THEN 'Restoring'
         WHEN state = 3 THEN 'Recovering'
         WHEN state = 4 THEN 'Recovery Pending'
         WHEN state = 5 THEN 'Suspect'
         WHEN state = 6 THEN 'EMERGENCY'
       END                 AS [State],
       create_date,
       compatibility_level,
       recovery_model_desc,
       physical_database_name
FROM sys.databases;

2. 检查数据库空间使用情况

SELECT DB_NAME(database_id) AS [Database Name],
       (size * 8) / 1024 AS [Total Size (MB)],
       ((size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8)) / 1024 AS [Available Space (MB)],
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8 / 1024 AS [Used Space (MB)],
       physical_name
FROM sys.database_files;

3. 检查数据库备份情况

SELECT database_name, backup_start_date, backup_finish_date, duration, backup_size, compressed_backup_size, backup_type, recovery_model, backup_set_id
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;

4. 检查数据库自动增长设置

SELECT DB_NAME(database_id) AS [Database Name],
       name AS [File Name],
       physical_name AS [Physical Name],
       CAST(size / 128.0 AS DECIMAL(10,2)) AS [Initial Size (MB)],
       CAST(growth / 128.0 AS DECIMAL(10,2)) AS [Growth (MB)],
       CASE
         WHEN is_percent_growth = 1 THEN 'Yes'
         ELSE 'No'
       END AS [Percent Growth],
       CASE
         WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(10)) + '%'
         ELSE CAST(growth / 128 AS VARCHAR(10)) + ' MB'
       END AS [Increment],
       CASE
         WHEN max_size = -1 THEN 'Unlimited'
         ELSE CAST(max_size / 128 AS VARCHAR(10)) + ' MB'
       END AS [Maximum Size],
       physical_database_name
FROM sys.database_files
WHERE type_desc = 'ROWS';

以上SQL都是常用的日常检查SQL，可以根据需要进行查看。

二、示例说明

下面以“检查数据库状态”为例，说明如何使用该SQL：

1. 连接到SQL Server Management Studio（SSMS）。
2. 执行上面的SQL语句。
3. 查看结果，可以了解数据库的状态。

以下是检查数据库状态的一个示例结果：

Database Name   State               create_date                 compatibility_level recovery_model_desc physical_database_name
--------------- ------------------- --------------------------- ------------------- ------------------ ---------------------
master          Online              2009-04-08 09:13:36.250     140                 FULL                C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
tempdb          Online              2019-10-18 14:49:26.193     140                 SIMPLE              C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
model           Online              2014-07-31 21:38:29.260     140                 FULL                C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\model.mdf
msdb            Online              2019-09-23 10:34:20.857     140                 SIMPLE              C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf
AdventureWorks  Online              2019-09-23 10:41:52.953     140                 FULL                C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf

(5 rows affected)

从上面的结果可以看出，当前数据库状态都是Online，说明数据库正常运行。

以上就是SQL Server DBA日常检查常用SQL的完整攻略及示例说明，希望对你有所帮助。