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

92Yang

于 2024-10-04 09:36:05 发布

阅读量80

点赞数

分类专栏： SQL 文章标签：数据库 sql sqlserver

原文链接：https://mp.weixin.qq.com/s/dKe0eRSl_wdG-SUBV0vY8g

版权

SQL 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

一、说明：

此脚本基于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 ' 结束巡检 '