SQL Server全面巡检脚本实现数据库性能与健康检查-开发者社区-阿里云

一、说明：

此脚本基于2014进行编写的，sql server每个版本的函数基本都有变化，其他版本需要进行修改测试。大家可以根据官方的文章进行相应参数的修改。

官网地址：

https://learn.microsoft.com/zh-cn/previous-versions/sql/

二、脚本执行过程展示：

1、新建查询

2、复制脚本

3、点击执行

4、查看结果

我们可以看到执行成功了，结果栏是执行sql结果的展示，消息栏是执行的结果打印，当然也可以将结果进行另存留档。

三、脚本内容：

print '----------------------------'
print ' 开始巡检 '
print '----------------------------'
print '1.查看数据库版本信息 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
use master
go
print ' '
print @@version
go
print ' '
print ' '
select cast(serverproperty('productversion') as varchar(30)) as 产品版本号,
cast(serverproperty('productlevel') as varchar(30)) as sp_level,
cast(serverproperty('edition') as varchar(30)) as 版本
go
print '2.SQLSERVER 最大连接数 '
print'----------------------------'
print '*********************************'
print ' '
print @@max_connections
go
print '3.输出当前活跃的用户 '
print'----------------------------'
print '*********************************'
SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1;
-- 关闭受影响的行数消息
SET NOCOUNT ON;
DECLARE @Result NVARCHAR(MAX) = ''
DECLARE @session_id INT
DECLARE @login_name NVARCHAR(128)
DECLARE @host_name NVARCHAR(128)
DECLARE @program_name NVARCHAR(128)
DECLARE @status NVARCHAR(30)
-- 游标遍历查询结果
DECLARE session_cursor CURSOR FOR
SELECT session_id, login_name, host_name, program_name, status
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
OPEN session_cursor
FETCH NEXT FROM session_cursor INTO @session_id, @login_name, @host_name, @program_name, @status
-- 打印列名作为标题行
PRINT 'Session ID' + CHAR(9) + 'Login Name' + CHAR(9) + 'Host Name' + CHAR(9) + 'Program Name' + CHAR(9) + 'Status'
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串
SET @Result = CAST(@session_id AS NVARCHAR(10)) + CHAR(9) +
@login_name + CHAR(9) +
@host_name + CHAR(9) +
@program_name + CHAR(9) +
@status
-- 打印结果
PRINT @Result
-- 获取下一条记录
FETCH NEXT FROM session_cursor INTO @session_id, @login_name, @host_name, @program_name, @status
END
CLOSE session_cursor
DEALLOCATE session_cursor
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '4.查看所有数据库名称及大小 '
print'----------------------------'
print '*********************************'
exec sp_helpdb
-- 关闭受影响的行数消息
SET NOCOUNT ON;
-- 创建临时表来捕获存储过程的输出
CREATE TABLE #HelpDbResult2 (
name NVARCHAR(128),
db_size NVARCHAR(50),
owner NVARCHAR(128),
dbid SMALLINT,
created DATETIME,
status NVARCHAR(512),
compatibility_level TINYINT
);
-- 将存储过程的输出插入到临时表
INSERT INTO #HelpDbResult2
EXEC sp_helpdb;
DECLARE @name NVARCHAR(128)
DECLARE @db_size NVARCHAR(50)
DECLARE @owner NVARCHAR(128)
DECLARE @dbid SMALLINT
DECLARE @created DATETIME
DECLARE @status1 NVARCHAR(512)
DECLARE @compatibility_level TINYINT
-- 游标遍历临时表中的结果
DECLARE helpdb_cursor CURSOR FOR
SELECT name, db_size, owner, dbid, created, status, compatibility_level
FROM #HelpDbResult2;
OPEN helpdb_cursor;
FETCH NEXT FROM helpdb_cursor INTO @name, @db_size, @owner, @dbid, @created, @status1, @compatibility_level;
-- 打印列名作为标题行
PRINT 'Database Name' + CHAR(9) + 'Size' + CHAR(9) + 'Owner' + CHAR(9) + 'Database ID' + CHAR(9) + 'Created' + CHAR(9) + 'Status' + CHAR(9) + 'Compatibility Level'
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串
PRINT @name + CHAR(9) + @db_size + CHAR(9) + @owner + CHAR(9) + CAST(@dbid AS NVARCHAR(10)) + CHAR(9) + CAST(@created AS NVARCHAR(20)) + CHAR(9) + @status + CHAR(9) + CAST(@compatibility_level AS NVARCHAR(3))
-- 获取下一条记录
FETCH NEXT FROM helpdb_cursor INTO @name, @db_size, @owner, @dbid, @created, @status1, @compatibility_level;
END
CLOSE helpdb_cursor;
DEALLOCATE helpdb_cursor;
-- 删除临时表
DROP TABLE #HelpDbResult2;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '5.查看数据库所在机器的操作系统参数 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
exec master..xp_msver
-- 创建临时表来捕获存储过程的输出结果
CREATE TABLE #XpMsverResult (
idx INT,
name NVARCHAR(128),
internal_value INT,
character_value NVARCHAR(256)
);
-- 将存储过程的输出插入到临时表中
INSERT INTO #XpMsverResult (idx, name, internal_value, character_value)
EXEC master..xp_msver;
DECLARE @idx INT
DECLARE @name2 NVARCHAR(128)
DECLARE @internal_value INT
DECLARE @character_value NVARCHAR(256)
DECLARE @Result4 NVARCHAR(MAX)
-- 游标遍历临时表中的结果
DECLARE xpmsver_cursor CURSOR FOR
SELECT idx, name, internal_value, character_value
FROM #XpMsverResult;
OPEN xpmsver_cursor;
FETCH NEXT FROM xpmsver_cursor INTO @idx, @name2, @internal_value, @character_value;
-- 打印列名作为标题行
PRINT 'Idx' + REPLICATE(' ', 6 - LEN('Idx')) +
'Name' + REPLICATE(' ', 30 - LEN('Name')) +
'Internal Value' + REPLICATE(' ', 20 - LEN('Internal Value')) +
'Character Value'
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串
SET @Result4 =
CAST(@idx AS NVARCHAR(10)) + REPLICATE(' ', 6 - LEN(CAST(@idx AS NVARCHAR(10)))) +
ISNULL(@name2, '') + REPLICATE(' ', 30 - LEN(ISNULL(@name2, ''))) +
ISNULL(CAST(@internal_value AS NVARCHAR(10)), '') + REPLICATE(' ', 20 - LEN(ISNULL(CAST(@internal_value AS NVARCHAR(10)), ''))) +
ISNULL(@character_value, '')
-- 打印结果
PRINT @Result
-- 获取下一条记录
FETCH NEXT FROM xpmsver_cursor INTO @idx, @name2, @internal_value, @character_value;
END
CLOSE xpmsver_cursor;
DEALLOCATE xpmsver_cursor;
-- 删除临时表
DROP TABLE #XpMsverResult;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '6.查看数据库启动的参数 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
SELECT
name,value,value_in_use
FROM
sys.configurations
WHERE
configuration_id IN (
SELECT
configuration_id
FROM
sys.configurations
WHERE
name LIKE '%recovery%' OR
name LIKE '%memory%' OR
name LIKE '%max degree of parallelism%' OR
name LIKE '%cost threshold for parallelism%'
)
order by configuration_id
Go
-- 创建临时表来捕获查询结果
CREATE TABLE #ConfigurationsResult (
name NVARCHAR(128),
value SQL_VARIANT,
value_in_use SQL_VARIANT
);
-- 将查询结果插入到临时表中
INSERT INTO #ConfigurationsResult (name, value, value_in_use)
SELECT
name, value, value_in_use
FROM
sys.configurations
WHERE
configuration_id IN (
SELECT
configuration_id
FROM
sys.configurations
WHERE
name LIKE '%recovery%' OR
name LIKE '%memory%' OR
name LIKE '%max degree of parallelism%' OR
name LIKE '%cost threshold for parallelism%'
)
ORDER BY configuration_id;
DECLARE @name3 NVARCHAR(128)
DECLARE @value5 SQL_VARIANT
DECLARE @value_in_use SQL_VARIANT
DECLARE @Result5 NVARCHAR(MAX)
-- 游标遍历临时表中的结果
DECLARE configurations_cursor CURSOR FOR
SELECT name, value, value_in_use
FROM #ConfigurationsResult;
OPEN configurations_cursor;
FETCH NEXT FROM configurations_cursor INTO @name3, @value5, @value_in_use;
-- 打印列名作为标题行
PRINT 'Name' + REPLICATE(' ', 50 - LEN('Name')) +
'Value' + REPLICATE(' ', 20 - LEN('Value')) +
'Value In Use'
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串
SET @Result5 =
ISNULL(@name3, '') + REPLICATE(' ', 50 - LEN(ISNULL(@name3, ''))) +
CAST(ISNULL(@value5, '') AS NVARCHAR) + REPLICATE(' ', 20 - LEN(CAST(ISNULL(@value5, '') AS NVARCHAR))) +
CAST(ISNULL(@value_in_use, '') AS NVARCHAR)
-- 打印结果
PRINT @Result5
-- 获取下一条记录
FETCH NEXT FROM configurations_cursor INTO @name3, @value5, @value_in_use;
END
CLOSE configurations_cursor;
DEALLOCATE configurations_cursor;
-- 删除临时表
DROP TABLE #ConfigurationsResult;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '7.查看数据库启动时间 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
select convert(varchar(30),login_time,120)
from master..sysprocesses where spid=1
-- 创建临时表来捕获查询结果
CREATE TABLE #SysProcessesResult (
login_time VARCHAR(30)
);
-- 将查询结果插入到临时表中
INSERT INTO #SysProcessesResult (login_time)
SELECT convert(varchar(30), login_time, 120)
FROM master..sysprocesses
WHERE spid = 1;
DECLARE @login_time VARCHAR(30)
DECLARE @Result3 NVARCHAR(MAX)
-- 游标遍历临时表中的结果
DECLARE sysprocesses_cursor CURSOR FOR
SELECT login_time
FROM #SysProcessesResult;
OPEN sysprocesses_cursor;
FETCH NEXT FROM sysprocesses_cursor INTO @login_time;
-- 打印列名作为标题行
PRINT 'Login Time' + REPLICATE(' ', 30 - LEN('Login Time'))
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串
SET @Result3 = ISNULL(@login_time, '') + REPLICATE(' ', 30 - LEN(ISNULL(@login_time, '')))
-- 打印结果
PRINT @Result3
-- 获取下一条记录
FETCH NEXT FROM sysprocesses_cursor INTO @login_time;
END
CLOSE sysprocesses_cursor;
DEALLOCATE sysprocesses_cursor;
-- 删除临时表
DROP TABLE #SysProcessesResult;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '8.查看数据库服务器名 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
select 'Server Name:'+ltrim(@@servername)
-- 创建临时表来捕获查询结果
CREATE TABLE #ServerNameResult3 (
ServerInfo2 NVARCHAR(128)
);
-- 将查询结果插入到临时表中
INSERT INTO #ServerNameResult3 (ServerInfo2)
SELECT 'Server Name:' + LTRIM(@@servername);
DECLARE @ServerInfo2 NVARCHAR(128)
DECLARE @Result6 NVARCHAR(MAX)
-- 游标遍历临时表中的结果
DECLARE servername_cursor CURSOR FOR
SELECT ServerInfo2
FROM #ServerNameResult3;
OPEN servername_cursor;
FETCH NEXT FROM servername_cursor INTO @ServerInfo2;
-- 打印列名作为标题行
PRINT 'Server Information'
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 将每列结果拼接成字符串并打印
PRINT ISNULL(@ServerInfo2, '')
-- 获取下一条记录
FETCH NEXT FROM servername_cursor INTO @ServerInfo2;
END
CLOSE servername_cursor;
DEALLOCATE servername_cursor;
-- 删除临时表
DROP TABLE #ServerNameResult3;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '9.查看数据库实例名 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
select 'Instance:'+ltrim(@@servicename)
-- 创建临时表来捕获查询结果
CREATE TABLE #InstanceResult (
InstanceInfo NVARCHAR(128)
);
-- 将查询结果插入到临时表中
INSERT INTO #InstanceResult (InstanceInfo)
SELECT 'Instance:' + LTRIM(@@servicename);
DECLARE @InstanceInfo NVARCHAR(128)
DECLARE @Result7 NVARCHAR(MAX)
-- 游标遍历临时表中的结果
DECLARE instance_cursor CURSOR FOR
SELECT InstanceInfo
FROM #InstanceResult;
OPEN instance_cursor;
FETCH NEXT FROM instance_cursor INTO @InstanceInfo;
-- 打印列名作为标题行
PRINT 'Instance Information'
-- 打印每行数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 拼接字符串并打印结果
PRINT ISNULL(@InstanceInfo, '')
-- 获取下一条记录
FETCH NEXT FROM instance_cursor INTO @InstanceInfo;
END
CLOSE instance_cursor;
DEALLOCATE instance_cursor;
-- 删除临时表
DROP TABLE #InstanceResult;
-- 恢复默认行为（显示受影响的行数消息）
SET NOCOUNT OFF;
print '10.查看数据库磁盘空间信息 '
print'----------------------------'
print '*********************************'
-- 关闭受影响的行数消息
SET NOCOUNT ON;
EXEC master.dbo.xp_fixeddrives
-- 步骤1: 创建一个用于存储 xp_fixeddrives 结果的临时表
CREATE TABLE #FixedDrives (
Drive CHAR(1),
FreeSpaceMB INT
);
INSERT INTO #FixedDrives (Drive, FreeSpaceMB)
EXEC master.dbo.xp_fixeddrives;
DECLARE @Drive CHAR(1);
DECLARE @FreeSpaceMB INT;
DECLARE @ResultString NVARCHAR(MAX) = 'Drive | Free Space (MB)' + CHAR(13) + CHAR(10) + '-------------------------';
DECLARE drive_cursor CURSOR FOR
SELECT Drive, FreeSpaceMB FROM #FixedDrives;
OPEN drive_cursor;
FETCH NEXT FROM drive_cursor INTO @Drive, @FreeSpaceMB;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ResultString = @ResultString + CHAR(13) + CHAR(10) + @Drive + ' | ' + CAST(@FreeSpaceMB AS NVARCHAR(50));
FETCH NEXT FROM drive_cursor INTO @Drive, @FreeSpaceMB;
END
CLOSE drive_cursor;
DEALLOCATE drive_cursor;
-- 打印结果字符串
PRINT @ResultString;
DROP TABLE #FixedDrives;
SET NOCOUNT OFF;
print '11.日志文件大小及使用情况 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
dbcc sqlperf(logspace)
-- 步骤: 创建一个用于存储 DBCC SQLPERF(logspace) 结果的临时表
CREATE TABLE #LogSpace (
DatabaseName VARCHAR(128),
LogSizeMB FLOAT,
LogSpaceUsedPct FLOAT,
Status INT
);
-- 打印正在执行的脚本
-- 步骤: 将 DBCC SQLPERF(logspace) 的结果插入到临时表中
INSERT INTO #LogSpace (DatabaseName, LogSizeMB, LogSpaceUsedPct, Status)
EXEC ('DBCC SQLPERF(logspace) WITH NO_INFOMSGS');
-- 步骤: 查询并生成结果字符串
DECLARE @DatabaseName VARCHAR(128);
DECLARE @LogSizeMB FLOAT;
DECLARE @LogSpaceUsedPct FLOAT;
DECLARE @Status INT;
DECLARE @ResultString1 NVARCHAR(MAX) = 'DatabaseName | LogSizeMB | LogSpaceUsedPct | Status' + CHAR(13) + CHAR(10) + '---------------------------------------------------';
DECLARE logspace_cursor CURSOR FOR
SELECT DatabaseName, LogSizeMB, LogSpaceUsedPct, Status FROM #LogSpace;
OPEN logspace_cursor;
FETCH NEXT FROM logspace_cursor INTO @DatabaseName, @LogSizeMB, @LogSpaceUsedPct, @Status;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ResultString = @ResultString1 + CHAR(13) + CHAR(10) + @DatabaseName + ' | ' + CAST(@LogSizeMB AS NVARCHAR(50)) + ' | ' + CAST(@LogSpaceUsedPct AS NVARCHAR(50)) + ' | ' + CAST(@Status AS NVARCHAR(50));
FETCH NEXT FROM logspace_cursor INTO @DatabaseName, @LogSizeMB, @LogSpaceUsedPct, @Status;
END
CLOSE logspace_cursor;
DEALLOCATE logspace_cursor;
-- 打印结果字符串
PRINT @ResultString;
-- 步骤: 删除临时表
DROP TABLE #LogSpace;
SET NOCOUNT OFF;
print '12.表的磁盘空间使用信息 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
-- 打印正在执行的脚本
PRINT 'Executing: SELECT @@total_read [读取磁盘次数], @@total_write [写入磁盘次数], @@total_errors [磁盘写入错误数], GETDATE() [当前时间]';
-- 步骤1: 创建一个用于存储查询结果的临时表
CREATE TABLE #DiskStats (
TotalRead INT,
TotalWrite INT,
TotalErrors INT,
CurrentTime DATETIME
);
-- 步骤2: 执行查询并将结果插入到临时表中
INSERT INTO #DiskStats (TotalRead, TotalWrite, TotalErrors, CurrentTime)
SELECT @@total_read, @@total_write, @@total_errors, GETDATE();
-- 步骤3: 查询并生成结果字符串
DECLARE @TotalRead INT;
DECLARE @TotalWrite INT;
DECLARE @TotalErrors INT;
DECLARE @CurrentTime DATETIME;
DECLARE @ResultString4 NVARCHAR(MAX);
DECLARE diskstats_cursor CURSOR FOR
SELECT TotalRead, TotalWrite, TotalErrors, CurrentTime FROM #DiskStats;
OPEN diskstats_cursor;
FETCH NEXT FROM diskstats_cursor INTO @TotalRead, @TotalWrite, @TotalErrors, @CurrentTime;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 初始化结果字符串
SET @ResultString4 = '读取磁盘次数 | 写入磁盘次数 | 磁盘写入错误数 | 当前时间' + CHAR(13) + CHAR(10) + '---------------------------------------------------' + CHAR(13) + CHAR(10);
-- 拼接结果字符串
SET @ResultString4 = @ResultString4 + CAST(@TotalRead AS NVARCHAR(50)) + ' | ' + CAST(@TotalWrite AS NVARCHAR(50)) + ' | ' + CAST(@TotalErrors AS NVARCHAR(50)) + ' | ' + CAST(@CurrentTime AS NVARCHAR(50));
FETCH NEXT FROM diskstats_cursor INTO @TotalRead, @TotalWrite, @TotalErrors, @CurrentTime;
END
CLOSE diskstats_cursor;
DEALLOCATE diskstats_cursor;
-- 打印结果字符串
PRINT @ResultString4;
-- 步骤4: 删除临时表
DROP TABLE #DiskStats;
SET NOCOUNT OFF;
print '13.获取I/O工作情况 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
select * from sys.dm_os_wait_stats
-- 创建用于存储查询结果的临时表
CREATE TABLE #WaitStats (
wait_type NVARCHAR(60),
waiting_tasks_count BIGINT,
wait_time_ms BIGINT,
max_wait_time_ms BIGINT,
signal_wait_time_ms BIGINT
);
-- 执行查询并将结果插入到临时表中
INSERT INTO #WaitStats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;
-- 声明变量用于存储每列的数据
DECLARE @wait_type NVARCHAR(60);
DECLARE @waiting_tasks_count BIGINT;
DECLARE @wait_time_ms BIGINT;
DECLARE @max_wait_time_ms BIGINT;
DECLARE @signal_wait_time_ms BIGINT;
DECLARE @ResultString6 NVARCHAR(MAX);
-- 初始化结果字符串的标题
SET @ResultString6 = 'Wait Stats:' + CHAR(13) + CHAR(10) +
'wait_type | waiting_tasks_count | wait_time_ms | max_wait_time_ms | signal_wait_time_ms' + CHAR(13) + CHAR(10) +
'-------------------------------------------------------------------------------';
-- 声明游标
DECLARE waitstats_cursor CURSOR FOR
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM #WaitStats;
-- 打开游标
OPEN waitstats_cursor;
-- 获取游标中的每一行数据并拼接到结果字符串中
FETCH NEXT FROM waitstats_cursor INTO @wait_type, @waiting_tasks_count, @wait_time_ms, @max_wait_time_ms, @signal_wait_time_ms;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ResultString6 = @ResultString + CHAR(13) + CHAR(10) +
@wait_type + ' | ' +
CAST(@waiting_tasks_count AS NVARCHAR(50)) + ' | ' +
CAST(@wait_time_ms AS NVARCHAR(50)) + ' | ' +
CAST(@max_wait_time_ms AS NVARCHAR(50)) + ' | ' +
CAST(@signal_wait_time_ms AS NVARCHAR(50));
FETCH NEXT FROM waitstats_cursor INTO @wait_type, @waiting_tasks_count, @wait_time_ms, @max_wait_time_ms, @signal_wait_time_ms;
END
-- 关闭游标
CLOSE waitstats_cursor;
DEALLOCATE waitstats_cursor;
-- 打印结果字符串
PRINT @ResultString;
-- 删除临时表
DROP TABLE #WaitStats;
select top 10 *, (s.total_logical_reads / s.execution_count) as avglogicalreads from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle)
order by avglogicalreads desc
select top 10 *, (s.total_logical_writes / s.execution_count) as avglogicalwrites from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle)
order by avglogicalwrites desc
select * from sys.dm_os_waiting_tasks
SET NOCOUNT ON;
-- 查询 sys.dm_os_waiting_tasks 并存储在临时表中
DECLARE @TempTable TABLE (
session_id INT,
exec_context_id INT,
wait_duration_ms BIGINT,
wait_type NVARCHAR(60),
blocking_task_address VARBINARY(8),
blocking_session_id INT,
resource_description NVARCHAR(256)
);
-- 插入查询结果到临时表中
INSERT INTO @TempTable
SELECT
session_id,
exec_context_id,
wait_duration_ms,
wait_type,
blocking_task_address,
blocking_session_id,
resource_description
FROM sys.dm_os_waiting_tasks;
-- 声明变量来存储每行的结果
DECLARE @session_id NVARCHAR(MAX);
DECLARE @exec_context_id NVARCHAR(MAX);
DECLARE @wait_duration_ms NVARCHAR(MAX);
DECLARE @wait_type2 NVARCHAR(MAX);
DECLARE @blocking_task_address NVARCHAR(MAX);
DECLARE @blocking_session_id NVARCHAR(MAX);
DECLARE @resource_description NVARCHAR(MAX);
-- 游标遍历临时表
DECLARE cur CURSOR FOR
SELECT
CAST(session_id AS NVARCHAR),
CAST(exec_context_id AS NVARCHAR),
CAST(wait_duration_ms AS NVARCHAR),
wait_type,
CAST(blocking_task_address AS NVARCHAR(MAX)),
CAST(blocking_session_id AS NVARCHAR),
resource_description
FROM @TempTable;
OPEN cur;
FETCH NEXT FROM cur INTO @session_id, @exec_context_id, @wait_duration_ms, @wait_type2, @blocking_task_address, @blocking_session_id, @resource_description;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化并打印每一行的结果
PRINT 'Session ID: ' + ISNULL(@session_id, '') + ' | ' +
'Exec Context ID: ' + ISNULL(@exec_context_id, '') + ' | ' +
'Wait Duration (ms): ' + ISNULL(@wait_duration_ms, '') + ' | ' +
'Wait Type: ' + ISNULL(@wait_type2, '') + ' | ' +
'Blocking Task Address: ' + ISNULL(@blocking_task_address, '') + ' | ' +
'Blocking Session ID: ' + ISNULL(@blocking_session_id, '') + ' | ' +
'Resource Description: ' + ISNULL(@resource_description, '');
PRINT '--------------------------------------------';
FETCH NEXT FROM cur INTO @session_id, @exec_context_id, @wait_duration_ms, @wait_type2, @blocking_task_address, @blocking_session_id, @resource_description;
END
CLOSE cur;
DEALLOCATE cur;
SET NOCOUNT OFF;
print '14.查看CPU活动及工作情况 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
select
@@cpu_busy,
@@timeticks [每个时钟周期对应的微秒数],
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],
getdate() [当前时间]
SET NOCOUNT ON;
-- 声明变量来存储查询结果
DECLARE @cpu_busy INT;
DECLARE @timeticks INT;
DECLARE @cpu_busy_sec FLOAT;
DECLARE @cpu_idle_sec FLOAT;
DECLARE @current_time DATETIME;
-- 获取查询结果
SELECT
@cpu_busy = @@cpu_busy,
@timeticks = @@timeticks,
@cpu_busy_sec = @@cpu_busy * CAST(@timeticks AS FLOAT) / 1000,
@cpu_idle_sec = @@idle * CAST(@timeticks AS FLOAT) / 1000,
@current_time = GETDATE();
-- 格式化并打印结果
PRINT 'CPU Busy: ' + CAST(@cpu_busy AS NVARCHAR);
PRINT 'Timeticks (us/clock tick): ' + CAST(@timeticks AS NVARCHAR);
PRINT 'CPU Busy Time (s): ' + CAST(@cpu_busy_sec AS NVARCHAR);
PRINT 'CPU Idle Time (s): ' + CAST(@cpu_idle_sec AS NVARCHAR);
PRINT 'Current Time: ' + CAST(@current_time AS NVARCHAR);
PRINT '-----------------------------------------';
-- 美观的多行输出
DECLARE @result NVARCHAR(MAX);
SET @result =
'CPU Busy: ' + CAST(@cpu_busy AS NVARCHAR) + CHAR(13) + CHAR(10) +
'Timeticks (us/clock tick): ' + CAST(@timeticks AS NVARCHAR) + CHAR(13) + CHAR(10) +
'CPU Busy Time (s): ' + CAST(@cpu_busy_sec AS NVARCHAR) + CHAR(13) + CHAR(10) +
'CPU Idle Time (s): ' + CAST(@cpu_idle_sec AS NVARCHAR) + CHAR(13) + CHAR(10) +
'Current Time: ' + CAST(@current_time AS NVARCHAR);
PRINT @result;
SET NOCOUNT OFF;
print '15.检查锁与等待 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
exec sp_lock
Go
SET NOCOUNT ON;
-- 创建临时表来存储 sp_lock 的结果
CREATE TABLE #LockInfo (
spid INT,
dbid INT,
ObjId BIGINT,
IndId INT,
Type NVARCHAR(4),
Resource NVARCHAR(32),
Mode NVARCHAR(8),
Status NVARCHAR(8)
);
-- 插入 sp_lock 的结果到临时表中
INSERT INTO #LockInfo
EXEC sp_lock;
-- 声明变量来存储每一行的结果
DECLARE @spid NVARCHAR(10);
DECLARE @dbid NVARCHAR(10);
DECLARE @ObjId NVARCHAR(20);
DECLARE @IndId NVARCHAR(10);
DECLARE @Type NVARCHAR(4);
DECLARE @Resource NVARCHAR(32);
DECLARE @Mode NVARCHAR(8);
DECLARE @Status NVARCHAR(8);
DECLARE @result NVARCHAR(MAX);
-- 游标遍历临时表
DECLARE cur CURSOR FOR
SELECT
CAST(spid AS NVARCHAR),
CAST(dbid AS NVARCHAR),
CAST(ObjId AS NVARCHAR),
CAST(IndId AS NVARCHAR),
Type,
Resource,
Mode,
Status
FROM #LockInfo;
OPEN cur;
FETCH NEXT FROM cur INTO @spid, @dbid, @ObjId, @IndId, @Type, @Resource, @Mode, @Status;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化并打印每一行的结果
SET @result = 'SPID: ' + @spid + ', ' +
'DBID: ' + @dbid + ', ' +
'ObjId: ' + @ObjId + ', ' +
'IndId: ' + @IndId + ', ' +
'Type: ' + @Type + ', ' +
'Resource: ' + @Resource + ', ' +
'Mode: ' + @Mode + ', ' +
'Status: ' + @Status;
PRINT @result;
FETCH NEXT FROM cur INTO @spid, @dbid, @ObjId, @IndId, @Type, @Resource, @Mode, @Status;
END
CLOSE cur;
DEALLOCATE cur;
-- 删除临时表
DROP TABLE #LockInfo;
SET NOCOUNT OFF;
print '16.检查死锁 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
exec sp_who2
SET NOCOUNT ON;
-- 创建用于存储 sp_who2 结果的临时表
CREATE TABLE #Who2 (
SPID INT,
Status NVARCHAR(255),
Login NVARCHAR(255),
HostName NVARCHAR(255),
BlkBy NVARCHAR(50),
DBName NVARCHAR(255),
Command NVARCHAR(255),
CPUTime INT,
DiskIO INT,
LastBatch NVARCHAR(255),
ProgramName NVARCHAR(255),
SPID2 INT, -- This is for the SPID in sp_who2 output
RequestID INT
);
-- 将 sp_who2 的结果插入到临时表中
INSERT INTO #Who2 (SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID2, RequestID)
EXEC sp_who2;
-- 声明变量来存储每一行的结果
DECLARE @SPID1 NVARCHAR(10);
DECLARE @Status11 NVARCHAR(255);
DECLARE @Login NVARCHAR(255);
DECLARE @HostName NVARCHAR(255);
DECLARE @BlkBy NVARCHAR(50);
DECLARE @DBName NVARCHAR(255);
DECLARE @Command NVARCHAR(255);
DECLARE @CPUTime NVARCHAR(10);
DECLARE @DiskIO NVARCHAR(10);
DECLARE @LastBatch NVARCHAR(255);
DECLARE @ProgramName NVARCHAR(255);
DECLARE @SPID2 NVARCHAR(10);
DECLARE @RequestID NVARCHAR(10);
DECLARE @result111 NVARCHAR(MAX);
-- 游标遍历临时表
DECLARE cur CURSOR FOR
SELECT
CAST(SPID AS NVARCHAR),
Status,
Login,
HostName,
BlkBy,
DBName,
Command,
CAST(CPUTime AS NVARCHAR),
CAST(DiskIO AS NVARCHAR),
LastBatch,
ProgramName,
CAST(SPID2 AS NVARCHAR),
CAST(RequestID AS NVARCHAR)
FROM #Who2;
OPEN cur;
FETCH NEXT FROM cur INTO @SPID1, @Status11, @Login, @HostName, @BlkBy, @DBName, @Command, @CPUTime, @DiskIO, @LastBatch, @ProgramName, @SPID2, @RequestID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化并打印每一行的结果
SET @result111 = 'SPID: ' + ISNULL(@SPID1, '') + ', ' +
'Status: ' + ISNULL(@Status11, '') + ', ' +
'Login: ' + ISNULL(@Login, '') + ', ' +
'HostName: ' + ISNULL(@HostName, '') + ', ' +
'BlkBy: ' + ISNULL(@BlkBy, '') + ', ' +
'DBName: ' + ISNULL(@DBName, '') + ', ' +
'Command: ' + ISNULL(@Command, '') + ', ' +
'CPUTime: ' + ISNULL(@CPUTime, '') + ', ' +
'DiskIO: ' + ISNULL(@DiskIO, '') + ', ' +
'LastBatch: ' + ISNULL(@LastBatch, '') + ', ' +
'ProgramName: ' + ISNULL(@ProgramName, '') + ', ' +
'SPID2: ' + ISNULL(@SPID2, '') + ', ' +
'RequestID: ' + ISNULL(@RequestID, '');
PRINT @result111;
FETCH NEXT FROM cur INTO @SPID1, @Status11, @Login, @HostName, @BlkBy, @DBName, @Command, @CPUTime, @DiskIO, @LastBatch, @ProgramName, @SPID2, @RequestID;
END
CLOSE cur;
DEALLOCATE cur;
-- 删除临时表
DROP TABLE #Who2;
SET NOCOUNT OFF;
print '17.活动用户和进程的信息 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
exec sp_who 'active'
SET NOCOUNT OFF;
print '18.查看所有数据库用户所属的角色信息 '
print'----------------------------'
print '*********************************'
exec sp_helpsrvrolemember
SET NOCOUNT ON;
-- 创建用于存储 sp_helpsrvrolemember 结果的临时表
CREATE TABLE #SrvRoleMember (
ServerRole NVARCHAR(255),
MemberName NVARCHAR(255),
MemberSID VARBINARY(MAX)
);
-- 将 sp_helpsrvrolemember 的结果插入到临时表中
INSERT INTO #SrvRoleMember (ServerRole, MemberName, MemberSID)
EXEC sp_helpsrvrolemember;
-- 声明变量来存储每一行的结果
DECLARE @ServerRole NVARCHAR(255);
DECLARE @MemberName NVARCHAR(255);
DECLARE @MemberSID NVARCHAR(MAX);
DECLARE @result99 NVARCHAR(MAX);
-- 将 MemberSID 转换为十六进制字符串
DECLARE @HexMemberSID NVARCHAR(MAX);
-- 游标遍历临时表
DECLARE cur CURSOR FOR
SELECT
ServerRole,
MemberName,
CONVERT(NVARCHAR(MAX), MemberSID, 1) AS MemberSID -- 使用样式 1 转换为十六进制字符串
FROM #SrvRoleMember;
OPEN cur;
FETCH NEXT FROM cur INTO @ServerRole, @MemberName, @MemberSID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化并打印每一行的结果
SET @HexMemberSID = CONVERT(NVARCHAR(MAX), @MemberSID, 1); -- 确保 MemberSID 显示为十六进制字符串
SET @result99 = 'ServerRole: ' + ISNULL(@ServerRole, '') + ', ' +
'MemberName: ' + ISNULL(@MemberName, '') + ', ' +
'MemberSID: ' + ISNULL(@HexMemberSID, '');
PRINT @result99;
FETCH NEXT FROM cur INTO @ServerRole, @MemberName, @MemberSID;
END
CLOSE cur;
DEALLOCATE cur;
-- 删除临时表
DROP TABLE #SrvRoleMember;
SET NOCOUNT OFF;
print '19.查看链接服务器 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
exec sp_helplinkedsrvlogin
SET NOCOUNT OFF;
print '20.查询文件组和文件 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
select
df.[name],df.physical_name,df.[size],df.growth,
f.[name][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id
Go
SET NOCOUNT ON;
-- 创建用于存储查询结果的临时表
CREATE TABLE #DatabaseFiles (
name NVARCHAR(255),
physical_name NVARCHAR(260),
size INT,
growth INT,
filegroup NVARCHAR(255),
is_default BIT
);
-- 将查询结果插入到临时表中
INSERT INTO #DatabaseFiles (name, physical_name, size, growth, filegroup, is_default)
SELECT
df.[name],
df.physical_name,
df.[size],
df.growth,
f.[name] AS [filegroup],
f.is_default
FROM sys.database_files df
JOIN sys.filegroups f ON df.data_space_id = f.data_space_id;
-- 声明变量来存储每一行的结果
DECLARE @name NVARCHAR(255);
DECLARE @physical_name NVARCHAR(260);
DECLARE @size NVARCHAR(10);
DECLARE @growth NVARCHAR(10);
DECLARE @filegroup NVARCHAR(255);
DECLARE @is_default NVARCHAR(5);
DECLARE @result NVARCHAR(MAX);
-- 游标遍历临时表
DECLARE cur CURSOR FOR
SELECT
name,
physical_name,
CAST(size AS NVARCHAR(10)),
CAST(growth AS NVARCHAR(10)),
filegroup,
CAST(is_default AS NVARCHAR(5))
FROM #DatabaseFiles;
OPEN cur;
FETCH NEXT FROM cur INTO @name, @physical_name, @size, @growth, @filegroup, @is_default;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化并打印每一行的结果
SET @result = 'Name: ' + ISNULL(@name, '') + ', ' +
'Physical Name: ' + ISNULL(@physical_name, '') + ', ' +
'Size: ' + ISNULL(@size, '') + ', ' +
'Growth: ' + ISNULL(@growth, '') + ', ' +
'Filegroup: ' + ISNULL(@filegroup, '') + ', ' +
'Is Default: ' + ISNULL(@is_default, '');
PRINT @result;
FETCH NEXT FROM cur INTO @name, @physical_name, @size, @growth, @filegroup, @is_default;
END
CLOSE cur;
DEALLOCATE cur;
-- 删除临时表
DROP TABLE #DatabaseFiles;
SET NOCOUNT OFF;
print '21.查看SQL Server的实际内存占用 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
select * from sysperfinfo where counter_name like '%Memory%'
-- 声明变量
DECLARE @counter_name NVARCHAR(128);
DECLARE @instance_name NVARCHAR(128);
DECLARE @cntr_value BIGINT;
DECLARE @row NVARCHAR(MAX);
-- 声明游标
DECLARE memory_cursor CURSOR FOR
SELECT counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Memory%';
-- 打开游标
OPEN memory_cursor;
-- 获取第一行数据
FETCH NEXT FROM memory_cursor INTO @counter_name, @instance_name, @cntr_value;
-- 打印列名
PRINT 'Counter Name | Instance Name | Counter Value';
-- 遍历游标中的数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 拼接每一行数据
SET @row = LEFT(@counter_name + SPACE(20), 20) + ' | '
+ LEFT(ISNULL(@instance_name, 'N/A') + SPACE(20), 20) + ' | '
+ CAST(@cntr_value AS NVARCHAR);
-- 打印当前行数据
PRINT @row;
-- 获取下一行数据
FETCH NEXT FROM memory_cursor INTO @counter_name, @instance_name, @cntr_value;
END
-- 关闭游标
CLOSE memory_cursor;
-- 释放游标
DEALLOCATE memory_cursor;
SET NOCOUNT OFF;
print '22.显示所有数据库的日志空间信息 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
dbcc sqlperf(logspace)
Go
-- 创建一个临时表来存储DBCC SQLPERF(LOGSPACE)的结果
CREATE TABLE #LogSpace (
[Database Name] NVARCHAR(128),
[Log Size (MB)] FLOAT,
[Log Space Used (%)] FLOAT,
[Status] INT
);
-- 插入DBCC SQLPERF(LOGSPACE)的结果到临时表
INSERT INTO #LogSpace
EXEC ('DBCC SQLPERF(LOGSPACE)');
-- 声明变量
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @LogSizeMB FLOAT;
DECLARE @LogSpaceUsedPercent FLOAT;
DECLARE @Status INT;
DECLARE @row NVARCHAR(MAX);
-- 声明游标
DECLARE logspace_cursor CURSOR FOR
SELECT [Database Name], [Log Size (MB)], [Log Space Used (%)], [Status]
FROM #LogSpace;
-- 打开游标
OPEN logspace_cursor;
-- 获取第一行数据
FETCH NEXT FROM logspace_cursor INTO @DatabaseName, @LogSizeMB, @LogSpaceUsedPercent, @Status;
-- 打印列名
PRINT 'Database Name | Log Size (MB) | Log Space Used (%) | Status';
-- 遍历游标中的数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 拼接每一行数据，并保证对齐
SET @row = LEFT(@DatabaseName + SPACE(25), 25) + ' | '
+ RIGHT(SPACE(20) + CAST(@LogSizeMB AS NVARCHAR), 20) + ' | '
+ RIGHT(SPACE(25) + CAST(@LogSpaceUsedPercent AS NVARCHAR), 25) + ' | '
+ CAST(@Status AS NVARCHAR);
-- 打印当前行数据
PRINT @row;
-- 获取下一行数据
FETCH NEXT FROM logspace_cursor INTO @DatabaseName, @LogSizeMB, @LogSpaceUsedPercent, @Status;
END
-- 关闭游标
CLOSE logspace_cursor;
-- 释放游标
DEALLOCATE logspace_cursor;
-- 删除临时表
DROP TABLE #LogSpace;
select *,CAST(cntr_value/1024.0 as decimal(20,1)) MemoryMB
from master.sys.sysperfinfo
where counter_name='Total Server Memory (KB)'
SET NOCOUNT OFF;
print '23.查询表空间的已使用大小 '
print'----------------------------'
print '*********************************'
SET NOCOUNT ON;
SELECT
DB_NAME() AS DatabaseName,
mf.name AS FileName,
mf.size * 8 / 1024 AS SizeMB,
mf.size * 8 / 1024 - FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024 AS FreeSpaceMB,
FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024 AS UsedSpaceMB
FROM
sys.master_files mf
WHERE
mf.database_id = DB_ID()
Go
-- 创建一个临时表来存储查询结果
CREATE TABLE #FileSpace (
DatabaseName NVARCHAR(128),
FileName NVARCHAR(128),
SizeMB DECIMAL(18, 2),
FreeSpaceMB DECIMAL(18, 2),
UsedSpaceMB DECIMAL(18, 2)
);
-- 插入查询结果到临时表
INSERT INTO #FileSpace
SELECT
DB_NAME() AS DatabaseName,
mf.name AS FileName,
mf.size * 8 / 1024 AS SizeMB,
mf.size * 8 / 1024 - FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024 AS FreeSpaceMB,
FILEPROPERTY(mf.name, 'SpaceUsed') * 8 / 1024 AS UsedSpaceMB
FROM
sys.master_files mf
WHERE
mf.database_id = DB_ID();
-- 声明变量
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @FileName NVARCHAR(128);
DECLARE @SizeMB DECIMAL(18, 2);
DECLARE @FreeSpaceMB DECIMAL(18, 2);
DECLARE @UsedSpaceMB DECIMAL(18, 2);
DECLARE @row NVARCHAR(MAX);
-- 声明游标
DECLARE file_cursor CURSOR FOR
SELECT DatabaseName, FileName, SizeMB, FreeSpaceMB, UsedSpaceMB
FROM #FileSpace;
-- 打开游标
OPEN file_cursor;
-- 获取第一行数据
FETCH NEXT FROM file_cursor INTO @DatabaseName, @FileName, @SizeMB, @FreeSpaceMB, @UsedSpaceMB;
-- 打印列名
PRINT 'Database Name | File Name | Size (MB) | Free Space (MB) | Used Space (MB)';
-- 遍历游标中的数据
WHILE @@FETCH_STATUS = 0
BEGIN
-- 拼接每一行数据，并保证对齐
SET @row = LEFT(@DatabaseName + SPACE(20), 20) + ' | '
+ LEFT(@FileName + SPACE(25), 25) + ' | '
+ RIGHT(SPACE(15) + CAST(@SizeMB AS NVARCHAR(15)), 15) + ' | '
+ RIGHT(SPACE(18) + CAST(@FreeSpaceMB AS NVARCHAR(18)), 18) + ' | '
+ RIGHT(SPACE(15) + CAST(@UsedSpaceMB AS NVARCHAR(15)), 15);
-- 打印当前行数据
PRINT @row;
-- 获取下一行数据
FETCH NEXT FROM file_cursor INTO @DatabaseName, @FileName, @SizeMB, @FreeSpaceMB, @UsedSpaceMB;
END
-- 关闭游标
CLOSE file_cursor;
-- 释放游标
DEALLOCATE file_cursor;
-- 删除临时表
DROP TABLE #FileSpace;
SET NOCOUNT OFF;
print '----------------------------'
print ' 结束巡检 '

全网最长的sql server巡检脚本分享（1000行）

一、说明：

二、脚本执行过程展示：

三、脚本内容：

热门文章

最新文章

相关课程

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

全网最长的sql server巡检脚本分享（1000行）

一、说明：

二、脚本执行过程展示：

三、脚本内容：

热门文章

最新文章

相关课程

相关电子书