SQL SERVER巡检脚本

print'----------------------------'
print'开始巡检'
print'----------------------------'
print'1.查看数据库版本信息'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
usemaster
go
print''
print@@version
go
print''
print''
selectcast(serverproperty('productversion')asvarchar(30))as产品版本号,
cast(serverproperty('productlevel')asvarchar(30))assp_level,
cast(serverproperty('edition')asvarchar(30))as版本
go
print'2.SQLSERVER最大连接数'
print'----------------------------'
print'*********************************'
print''
print@@max_connections
go
print'3.输出当前活跃的用户'
print'----------------------------'
print'*********************************'
SELECT*FROMsys.dm_exec_sessionsWHEREis_user_process=1;
--关闭受影响的行数消息
SETNOCOUNTON;
DECLARE@ResultNVARCHAR(MAX)=''
DECLARE@session_idINT
DECLARE@login_nameNVARCHAR(128)
DECLARE@host_nameNVARCHAR(128)
DECLARE@program_nameNVARCHAR(128)
DECLARE@statusNVARCHAR(30)
--游标遍历查询结果
DECLAREsession_cursorCURSORFOR
SELECTsession_id,login_name,host_name,program_name,status
FROMsys.dm_exec_sessions
WHEREis_user_process=1
OPENsession_cursor
FETCHNEXTFROMsession_cursorINTO@session_id,@login_name,@host_name,@program_name,@status
--打印列名作为标题行
PRINT'SessionID'+CHAR(9)+'LoginName'+CHAR(9)+'HostName'+CHAR(9)+'ProgramName'+CHAR(9)+'Status'
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串
SET@Result=CAST(@session_idASNVARCHAR(10))+CHAR(9)+
@login_name+CHAR(9)+
@host_name+CHAR(9)+
@program_name+CHAR(9)+
@status
--打印结果
PRINT@Result
--获取下一条记录
FETCHNEXTFROMsession_cursorINTO@session_id,@login_name,@host_name,@program_name,@status
END
CLOSEsession_cursor
DEALLOCATEsession_cursor
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'4.查看所有数据库名称及大小'
print'----------------------------'
print'*********************************'
execsp_helpdb
--关闭受影响的行数消息
SETNOCOUNTON;
--创建临时表来捕获存储过程的输出
CREATETABLE#HelpDbResult2(
nameNVARCHAR(128),
db_sizeNVARCHAR(50),
ownerNVARCHAR(128),
dbidSMALLINT,
createdDATETIME,
statusNVARCHAR(512),
compatibility_levelTINYINT
);
--将存储过程的输出插入到临时表
INSERTINTO#HelpDbResult2
EXECsp_helpdb;
DECLARE@nameNVARCHAR(128)
DECLARE@db_sizeNVARCHAR(50)
DECLARE@ownerNVARCHAR(128)
DECLARE@dbidSMALLINT
DECLARE@createdDATETIME
DECLARE@status1NVARCHAR(512)
DECLARE@compatibility_levelTINYINT
--游标遍历临时表中的结果
DECLAREhelpdb_cursorCURSORFOR
SELECTname,db_size,owner,dbid,created,status,compatibility_level
FROM#HelpDbResult2;
OPENhelpdb_cursor;
FETCHNEXTFROMhelpdb_cursorINTO@name,@db_size,@owner,@dbid,@created,@status1,@compatibility_level;
--打印列名作为标题行
PRINT'DatabaseName'+CHAR(9)+'Size'+CHAR(9)+'Owner'+CHAR(9)+'DatabaseID'+CHAR(9)+'Created'+CHAR(9)+'Status'+CHAR(9)+'CompatibilityLevel'
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串
PRINT@name+CHAR(9)+@db_size+CHAR(9)+@owner+CHAR(9)+CAST(@dbidASNVARCHAR(10))+CHAR(9)+CAST(@createdASNVARCHAR(20))+CHAR(9)+@status+CHAR(9)+CAST(@compatibility_levelASNVARCHAR(3))
--获取下一条记录
FETCHNEXTFROMhelpdb_cursorINTO@name,@db_size,@owner,@dbid,@created,@status1,@compatibility_level;
END
CLOSEhelpdb_cursor;
DEALLOCATEhelpdb_cursor;
--删除临时表
DROPTABLE#HelpDbResult2;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'5.查看数据库所在机器的操作系统参数'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
execmaster..xp_msver
--创建临时表来捕获存储过程的输出结果
CREATETABLE#XpMsverResult(
idxINT,
nameNVARCHAR(128),
internal_valueINT,
character_valueNVARCHAR(256)
);
--将存储过程的输出插入到临时表中
INSERTINTO#XpMsverResult(idx,name,internal_value,character_value)
EXECmaster..xp_msver;
DECLARE@idxINT
DECLARE@name2NVARCHAR(128)
DECLARE@internal_valueINT
DECLARE@character_valueNVARCHAR(256)
DECLARE@Result4NVARCHAR(MAX)
--游标遍历临时表中的结果
DECLARExpmsver_cursorCURSORFOR
SELECTidx,name,internal_value,character_value
FROM#XpMsverResult;
OPENxpmsver_cursor;
FETCHNEXTFROMxpmsver_cursorINTO@idx,@name2,@internal_value,@character_value;
--打印列名作为标题行
PRINT'Idx'+REPLICATE('',6-LEN('Idx'))+
'Name'+REPLICATE('',30-LEN('Name'))+
'InternalValue'+REPLICATE('',20-LEN('InternalValue'))+
'CharacterValue'
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串
SET@Result4=
CAST(@idxASNVARCHAR(10))+REPLICATE('',6-LEN(CAST(@idxASNVARCHAR(10))))+
ISNULL(@name2,'')+REPLICATE('',30-LEN(ISNULL(@name2,'')))+
ISNULL(CAST(@internal_valueASNVARCHAR(10)),'')+REPLICATE('',20-LEN(ISNULL(CAST(@internal_valueASNVARCHAR(10)),'')))+
ISNULL(@character_value,'')
--打印结果
PRINT@Result
--获取下一条记录
FETCHNEXTFROMxpmsver_cursorINTO@idx,@name2,@internal_value,@character_value;
END
CLOSExpmsver_cursor;
DEALLOCATExpmsver_cursor;
--删除临时表
DROPTABLE#XpMsverResult;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'6.查看数据库启动的参数'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
SELECT
name,value,value_in_use
FROM
sys.configurations
WHERE
configuration_idIN(
SELECT
configuration_id
FROM
sys.configurations
WHERE
nameLIKE'%recovery%'OR
nameLIKE'%memory%'OR
nameLIKE'%maxdegreeofparallelism%'OR
nameLIKE'%costthresholdforparallelism%'
)
orderbyconfiguration_id
Go
--创建临时表来捕获查询结果
CREATETABLE#ConfigurationsResult(
nameNVARCHAR(128),
valueSQL_VARIANT,
value_in_useSQL_VARIANT
);
--将查询结果插入到临时表中
INSERTINTO#ConfigurationsResult(name,value,value_in_use)
SELECT
name,value,value_in_use
FROM
sys.configurations
WHERE
configuration_idIN(
SELECT
configuration_id
FROM
sys.configurations
WHERE
nameLIKE'%recovery%'OR
nameLIKE'%memory%'OR
nameLIKE'%maxdegreeofparallelism%'OR
nameLIKE'%costthresholdforparallelism%'
)
ORDERBYconfiguration_id;
DECLARE@name3NVARCHAR(128)
DECLARE@value5SQL_VARIANT
DECLARE@value_in_useSQL_VARIANT
DECLARE@Result5NVARCHAR(MAX)
--游标遍历临时表中的结果
DECLAREconfigurations_cursorCURSORFOR
SELECTname,value,value_in_use
FROM#ConfigurationsResult;
OPENconfigurations_cursor;
FETCHNEXTFROMconfigurations_cursorINTO@name3,@value5,@value_in_use;
--打印列名作为标题行
PRINT'Name'+REPLICATE('',50-LEN('Name'))+
'Value'+REPLICATE('',20-LEN('Value'))+
'ValueInUse'
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串
SET@Result5=
ISNULL(@name3,'')+REPLICATE('',50-LEN(ISNULL(@name3,'')))+
CAST(ISNULL(@value5,'')ASNVARCHAR)+REPLICATE('',20-LEN(CAST(ISNULL(@value5,'')ASNVARCHAR)))+
CAST(ISNULL(@value_in_use,'')ASNVARCHAR)
--打印结果
PRINT@Result5
--获取下一条记录
FETCHNEXTFROMconfigurations_cursorINTO@name3,@value5,@value_in_use;
END
CLOSEconfigurations_cursor;
DEALLOCATEconfigurations_cursor;
--删除临时表
DROPTABLE#ConfigurationsResult;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'7.查看数据库启动时间'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
selectconvert(varchar(30),login_time,120)
frommaster..sysprocesseswherespid=1
--创建临时表来捕获查询结果
CREATETABLE#SysProcessesResult(
login_timeVARCHAR(30)
);
--将查询结果插入到临时表中
INSERTINTO#SysProcessesResult(login_time)
SELECTconvert(varchar(30),login_time,120)
FROMmaster..sysprocesses
WHEREspid=1;
DECLARE@login_timeVARCHAR(30)
DECLARE@Result3NVARCHAR(MAX)
--游标遍历临时表中的结果
DECLAREsysprocesses_cursorCURSORFOR
SELECTlogin_time
FROM#SysProcessesResult;
OPENsysprocesses_cursor;
FETCHNEXTFROMsysprocesses_cursorINTO@login_time;
--打印列名作为标题行
PRINT'LoginTime'+REPLICATE('',30-LEN('LoginTime'))
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串
SET@Result3=ISNULL(@login_time,'')+REPLICATE('',30-LEN(ISNULL(@login_time,'')))
--打印结果
PRINT@Result3
--获取下一条记录
FETCHNEXTFROMsysprocesses_cursorINTO@login_time;
END
CLOSEsysprocesses_cursor;
DEALLOCATEsysprocesses_cursor;
--删除临时表
DROPTABLE#SysProcessesResult;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'8.查看数据库服务器名'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
select'ServerName:'+ltrim(@@servername)
--创建临时表来捕获查询结果
CREATETABLE#ServerNameResult3(
ServerInfo2NVARCHAR(128)
);
--将查询结果插入到临时表中
INSERTINTO#ServerNameResult3(ServerInfo2)
SELECT'ServerName:'+LTRIM(@@servername);
DECLARE@ServerInfo2NVARCHAR(128)
DECLARE@Result6NVARCHAR(MAX)
--游标遍历临时表中的结果
DECLAREservername_cursorCURSORFOR
SELECTServerInfo2
FROM#ServerNameResult3;
OPENservername_cursor;
FETCHNEXTFROMservername_cursorINTO@ServerInfo2;
--打印列名作为标题行
PRINT'ServerInformation'
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--将每列结果拼接成字符串并打印
PRINTISNULL(@ServerInfo2,'')
--获取下一条记录
FETCHNEXTFROMservername_cursorINTO@ServerInfo2;
END
CLOSEservername_cursor;
DEALLOCATEservername_cursor;
--删除临时表
DROPTABLE#ServerNameResult3;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'9.查看数据库实例名'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
select'Instance:'+ltrim(@@servicename)
--创建临时表来捕获查询结果
CREATETABLE#InstanceResult(
InstanceInfoNVARCHAR(128)
);
--将查询结果插入到临时表中
INSERTINTO#InstanceResult(InstanceInfo)
SELECT'Instance:'+LTRIM(@@servicename);
DECLARE@InstanceInfoNVARCHAR(128)
DECLARE@Result7NVARCHAR(MAX)
--游标遍历临时表中的结果
DECLAREinstance_cursorCURSORFOR
SELECTInstanceInfo
FROM#InstanceResult;
OPENinstance_cursor;
FETCHNEXTFROMinstance_cursorINTO@InstanceInfo;
--打印列名作为标题行
PRINT'InstanceInformation'
--打印每行数据
WHILE@@FETCH_STATUS=0
BEGIN
--拼接字符串并打印结果
PRINTISNULL(@InstanceInfo,'')
--获取下一条记录
FETCHNEXTFROMinstance_cursorINTO@InstanceInfo;
END
CLOSEinstance_cursor;
DEALLOCATEinstance_cursor;
--删除临时表
DROPTABLE#InstanceResult;
--恢复默认行为（显示受影响的行数消息）
SETNOCOUNTOFF;
print'10.查看数据库磁盘空间信息'
print'----------------------------'
print'*********************************'
--关闭受影响的行数消息
SETNOCOUNTON;
EXECmaster.dbo.xp_fixeddrives
--步骤1:创建一个用于存储xp_fixeddrives结果的临时表
CREATETABLE#FixedDrives(
DriveCHAR(1),
FreeSpaceMBINT
);
INSERTINTO#FixedDrives(Drive,FreeSpaceMB)
EXECmaster.dbo.xp_fixeddrives;
DECLARE@DriveCHAR(1);
DECLARE@FreeSpaceMBINT;
DECLARE@ResultStringNVARCHAR(MAX)='Drive|FreeSpace(MB)'+CHAR(13)+CHAR(10)+'-------------------------';
DECLAREdrive_cursorCURSORFOR
SELECTDrive,FreeSpaceMBFROM#FixedDrives;
OPENdrive_cursor;
FETCHNEXTFROMdrive_cursorINTO@Drive,@FreeSpaceMB;
WHILE@@FETCH_STATUS=0
BEGIN
SET@ResultString=@ResultString+CHAR(13)+CHAR(10)+@Drive+'|'+CAST(@FreeSpaceMBASNVARCHAR(50));
FETCHNEXTFROMdrive_cursorINTO@Drive,@FreeSpaceMB;
END
CLOSEdrive_cursor;
DEALLOCATEdrive_cursor;
--打印结果字符串
PRINT@ResultString;
DROPTABLE#FixedDrives;
SETNOCOUNTOFF;
print'11.日志文件大小及使用情况'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
dbccsqlperf(logspace)
--步骤:创建一个用于存储DBCCSQLPERF(logspace)结果的临时表
CREATETABLE#LogSpace(
DatabaseNameVARCHAR(128),
LogSizeMBFLOAT,
LogSpaceUsedPctFLOAT,
StatusINT
);
--打印正在执行的脚本
--步骤:将DBCCSQLPERF(logspace)的结果插入到临时表中
INSERTINTO#LogSpace(DatabaseName,LogSizeMB,LogSpaceUsedPct,Status)
EXEC('DBCCSQLPERF(logspace)WITHNO_INFOMSGS');
--步骤:查询并生成结果字符串
DECLARE@DatabaseNameVARCHAR(128);
DECLARE@LogSizeMBFLOAT;
DECLARE@LogSpaceUsedPctFLOAT;
DECLARE@StatusINT;
DECLARE@ResultString1NVARCHAR(MAX)='DatabaseName|LogSizeMB|LogSpaceUsedPct|Status'+CHAR(13)+CHAR(10)+'---------------------------------------------------';
DECLARElogspace_cursorCURSORFOR
SELECTDatabaseName,LogSizeMB,LogSpaceUsedPct,StatusFROM#LogSpace;
OPENlogspace_cursor;
FETCHNEXTFROMlogspace_cursorINTO@DatabaseName,@LogSizeMB,@LogSpaceUsedPct,@Status;
WHILE@@FETCH_STATUS=0
BEGIN
SET@ResultString=@ResultString1+CHAR(13)+CHAR(10)+@DatabaseName+'|'+CAST(@LogSizeMBASNVARCHAR(50))+'|'+CAST(@LogSpaceUsedPctASNVARCHAR(50))+'|'+CAST(@StatusASNVARCHAR(50));
FETCHNEXTFROMlogspace_cursorINTO@DatabaseName,@LogSizeMB,@LogSpaceUsedPct,@Status;
END
CLOSElogspace_cursor;
DEALLOCATElogspace_cursor;
--打印结果字符串
PRINT@ResultString;
--步骤:删除临时表
DROPTABLE#LogSpace;
SETNOCOUNTOFF;
print'12.表的磁盘空间使用信息'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
--打印正在执行的脚本
PRINT'Executing:SELECT@@total_read[读取磁盘次数],@@total_write[写入磁盘次数],@@total_errors[磁盘写入错误数],GETDATE()[当前时间]';
--步骤1:创建一个用于存储查询结果的临时表
CREATETABLE#DiskStats(
TotalReadINT,
TotalWriteINT,
TotalErrorsINT,
CurrentTimeDATETIME
);
--步骤2:执行查询并将结果插入到临时表中
INSERTINTO#DiskStats(TotalRead,TotalWrite,TotalErrors,CurrentTime)
SELECT@@total_read,@@total_write,@@total_errors,GETDATE();
--步骤3:查询并生成结果字符串
DECLARE@TotalReadINT;
DECLARE@TotalWriteINT;
DECLARE@TotalErrorsINT;
DECLARE@CurrentTimeDATETIME;
DECLARE@ResultString4NVARCHAR(MAX);
DECLAREdiskstats_cursorCURSORFOR
SELECTTotalRead,TotalWrite,TotalErrors,CurrentTimeFROM#DiskStats;
OPENdiskstats_cursor;
FETCHNEXTFROMdiskstats_cursorINTO@TotalRead,@TotalWrite,@TotalErrors,@CurrentTime;
WHILE@@FETCH_STATUS=0
BEGIN
--初始化结果字符串
SET@ResultString4='读取磁盘次数|写入磁盘次数|磁盘写入错误数|当前时间'+CHAR(13)+CHAR(10)+'---------------------------------------------------'+CHAR(13)+CHAR(10);
--拼接结果字符串
SET@ResultString4=@ResultString4+CAST(@TotalReadASNVARCHAR(50))+'|'+CAST(@TotalWriteASNVARCHAR(50))+'|'+CAST(@TotalErrorsASNVARCHAR(50))+'|'+CAST(@CurrentTimeASNVARCHAR(50));
FETCHNEXTFROMdiskstats_cursorINTO@TotalRead,@TotalWrite,@TotalErrors,@CurrentTime;
END
CLOSEdiskstats_cursor;
DEALLOCATEdiskstats_cursor;
--打印结果字符串
PRINT@ResultString4;
--步骤4:删除临时表
DROPTABLE#DiskStats;
SETNOCOUNTOFF;
print'13.获取I/O工作情况'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
select*fromsys.dm_os_wait_stats
--创建用于存储查询结果的临时表
CREATETABLE#WaitStats(
wait_typeNVARCHAR(60),
waiting_tasks_countBIGINT,
wait_time_msBIGINT,
max_wait_time_msBIGINT,
signal_wait_time_msBIGINT
);
--执行查询并将结果插入到临时表中
INSERTINTO#WaitStats(wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms)
SELECTwait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms
FROMsys.dm_os_wait_stats;
--声明变量用于存储每列的数据
DECLARE@wait_typeNVARCHAR(60);
DECLARE@waiting_tasks_countBIGINT;
DECLARE@wait_time_msBIGINT;
DECLARE@max_wait_time_msBIGINT;
DECLARE@signal_wait_time_msBIGINT;
DECLARE@ResultString6NVARCHAR(MAX);
--初始化结果字符串的标题
SET@ResultString6='WaitStats:'+CHAR(13)+CHAR(10)+
'wait_type|waiting_tasks_count|wait_time_ms|max_wait_time_ms|signal_wait_time_ms'+CHAR(13)+CHAR(10)+
'-------------------------------------------------------------------------------';
--声明游标
DECLAREwaitstats_cursorCURSORFOR
SELECTwait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_msFROM#WaitStats;
--打开游标
OPENwaitstats_cursor;
--获取游标中的每一行数据并拼接到结果字符串中
FETCHNEXTFROMwaitstats_cursorINTO@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_countASNVARCHAR(50))+'|'+
CAST(@wait_time_msASNVARCHAR(50))+'|'+
CAST(@max_wait_time_msASNVARCHAR(50))+'|'+
CAST(@signal_wait_time_msASNVARCHAR(50));
FETCHNEXTFROMwaitstats_cursorINTO@wait_type,@waiting_tasks_count,@wait_time_ms,@max_wait_time_ms,@signal_wait_time_ms;
END
--关闭游标
CLOSEwaitstats_cursor;
DEALLOCATEwaitstats_cursor;
--打印结果字符串
PRINT@ResultString;
--删除临时表
DROPTABLE#WaitStats;
selecttop10*,(s.total_logical_reads/s.execution_count)asavglogicalreadsfromsys.dm_exec_query_statss
crossapplysys.dm_exec_sql_text(s.sql_handle)
orderbyavglogicalreadsdesc
selecttop10*,(s.total_logical_writes/s.execution_count)asavglogicalwritesfromsys.dm_exec_query_statss
crossapplysys.dm_exec_sql_text(s.sql_handle)
orderbyavglogicalwritesdesc
select*fromsys.dm_os_waiting_tasks
SETNOCOUNTON;
--查询sys.dm_os_waiting_tasks并存储在临时表中
DECLARE@TempTableTABLE(
session_idINT,
exec_context_idINT,
wait_duration_msBIGINT,
wait_typeNVARCHAR(60),
blocking_task_addressVARBINARY(8),
blocking_session_idINT,
resource_descriptionNVARCHAR(256)
);
--插入查询结果到临时表中
INSERTINTO@TempTable
SELECT
session_id,
exec_context_id,
wait_duration_ms,
wait_type,
blocking_task_address,
blocking_session_id,
resource_description
FROMsys.dm_os_waiting_tasks;
--声明变量来存储每行的结果
DECLARE@session_idNVARCHAR(MAX);
DECLARE@exec_context_idNVARCHAR(MAX);
DECLARE@wait_duration_msNVARCHAR(MAX);
DECLARE@wait_type2NVARCHAR(MAX);
DECLARE@blocking_task_addressNVARCHAR(MAX);
DECLARE@blocking_session_idNVARCHAR(MAX);
DECLARE@resource_descriptionNVARCHAR(MAX);
--游标遍历临时表
DECLAREcurCURSORFOR
SELECT
CAST(session_idASNVARCHAR),
CAST(exec_context_idASNVARCHAR),
CAST(wait_duration_msASNVARCHAR),
wait_type,
CAST(blocking_task_addressASNVARCHAR(MAX)),
CAST(blocking_session_idASNVARCHAR),
resource_description
FROM@TempTable;
OPENcur;
FETCHNEXTFROMcurINTO@session_id,@exec_context_id,@wait_duration_ms,@wait_type2,@blocking_task_address,@blocking_session_id,@resource_description;
WHILE@@FETCH_STATUS=0
BEGIN
--格式化并打印每一行的结果
PRINT'SessionID:'+ISNULL(@session_id,'')+'|'+
'ExecContextID:'+ISNULL(@exec_context_id,'')+'|'+
'WaitDuration(ms):'+ISNULL(@wait_duration_ms,'')+'|'+
'WaitType:'+ISNULL(@wait_type2,'')+'|'+
'BlockingTaskAddress:'+ISNULL(@blocking_task_address,'')+'|'+
'BlockingSessionID:'+ISNULL(@blocking_session_id,'')+'|'+
'ResourceDescription:'+ISNULL(@resource_description,'');
PRINT'--------------------------------------------';
FETCHNEXTFROMcurINTO@session_id,@exec_context_id,@wait_duration_ms,@wait_type2,@blocking_task_address,@blocking_session_id,@resource_description;
END
CLOSEcur;
DEALLOCATEcur;
SETNOCOUNTOFF;
print'14.查看CPU活动及工作情况'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
select
@@cpu_busy,
@@timeticks[每个时钟周期对应的微秒数],
@@cpu_busy*cast(@@timeticksasfloat)/1000[CPU工作时间(秒)],
@@idle*cast(@@timeticksasfloat)/1000[CPU空闲时间(秒)],
getdate()[当前时间]
SETNOCOUNTON;
--声明变量来存储查询结果
DECLARE@cpu_busyINT;
DECLARE@timeticksINT;
DECLARE@cpu_busy_secFLOAT;
DECLARE@cpu_idle_secFLOAT;
DECLARE@current_timeDATETIME;
--获取查询结果
SELECT
@cpu_busy=@@cpu_busy,
@timeticks=@@timeticks,
@cpu_busy_sec=@@cpu_busy*CAST(@timeticksASFLOAT)/1000,
@cpu_idle_sec=@@idle*CAST(@timeticksASFLOAT)/1000,
@current_time=GETDATE();
--格式化并打印结果
PRINT'CPUBusy:'+CAST(@cpu_busyASNVARCHAR);
PRINT'Timeticks(us/clocktick):'+CAST(@timeticksASNVARCHAR);
PRINT'CPUBusyTime(s):'+CAST(@cpu_busy_secASNVARCHAR);
PRINT'CPUIdleTime(s):'+CAST(@cpu_idle_secASNVARCHAR);
PRINT'CurrentTime:'+CAST(@current_timeASNVARCHAR);
PRINT'-----------------------------------------';
--美观的多行输出
DECLARE@resultNVARCHAR(MAX);
SET@result=
'CPUBusy:'+CAST(@cpu_busyASNVARCHAR)+CHAR(13)+CHAR(10)+
'Timeticks(us/clocktick):'+CAST(@timeticksASNVARCHAR)+CHAR(13)+CHAR(10)+
'CPUBusyTime(s):'+CAST(@cpu_busy_secASNVARCHAR)+CHAR(13)+CHAR(10)+
'CPUIdleTime(s):'+CAST(@cpu_idle_secASNVARCHAR)+CHAR(13)+CHAR(10)+
'CurrentTime:'+CAST(@current_timeASNVARCHAR);
PRINT@result;
SETNOCOUNTOFF;
print'15.检查锁与等待'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
execsp_lock
Go
SETNOCOUNTON;
--创建临时表来存储sp_lock的结果
CREATETABLE#LockInfo(
spidINT,
dbidINT,
ObjIdBIGINT,
IndIdINT,
TypeNVARCHAR(4),
ResourceNVARCHAR(32),
ModeNVARCHAR(8),
StatusNVARCHAR(8)
);
--插入sp_lock的结果到临时表中
INSERTINTO#LockInfo
EXECsp_lock;
--声明变量来存储每一行的结果
DECLARE@spidNVARCHAR(10);
DECLARE@dbidNVARCHAR(10);
DECLARE@ObjIdNVARCHAR(20);
DECLARE@IndIdNVARCHAR(10);
DECLARE@TypeNVARCHAR(4);
DECLARE@ResourceNVARCHAR(32);
DECLARE@ModeNVARCHAR(8);
DECLARE@StatusNVARCHAR(8);
DECLARE@resultNVARCHAR(MAX);
--游标遍历临时表
DECLAREcurCURSORFOR
SELECT
CAST(spidASNVARCHAR),
CAST(dbidASNVARCHAR),
CAST(ObjIdASNVARCHAR),
CAST(IndIdASNVARCHAR),
Type,
Resource,
Mode,
Status
FROM#LockInfo;
OPENcur;
FETCHNEXTFROMcurINTO@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;
FETCHNEXTFROMcurINTO@spid,@dbid,@ObjId,@IndId,@Type,@Resource,@Mode,@Status;
END
CLOSEcur;
DEALLOCATEcur;
--删除临时表
DROPTABLE#LockInfo;
SETNOCOUNTOFF;
print'16.检查死锁'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
execsp_who2
SETNOCOUNTON;
--创建用于存储sp_who2结果的临时表
CREATETABLE#Who2(
SPIDINT,
StatusNVARCHAR(255),
LoginNVARCHAR(255),
HostNameNVARCHAR(255),
BlkByNVARCHAR(50),
DBNameNVARCHAR(255),
CommandNVARCHAR(255),
CPUTimeINT,
DiskIOINT,
LastBatchNVARCHAR(255),
ProgramNameNVARCHAR(255),
SPID2INT,--ThisisfortheSPIDinsp_who2output
RequestIDINT
);
--将sp_who2的结果插入到临时表中
INSERTINTO#Who2(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPID2,RequestID)
EXECsp_who2;
--声明变量来存储每一行的结果
DECLARE@SPID1NVARCHAR(10);
DECLARE@Status11NVARCHAR(255);
DECLARE@LoginNVARCHAR(255);
DECLARE@HostNameNVARCHAR(255);
DECLARE@BlkByNVARCHAR(50);
DECLARE@DBNameNVARCHAR(255);
DECLARE@CommandNVARCHAR(255);
DECLARE@CPUTimeNVARCHAR(10);
DECLARE@DiskIONVARCHAR(10);
DECLARE@LastBatchNVARCHAR(255);
DECLARE@ProgramNameNVARCHAR(255);
DECLARE@SPID2NVARCHAR(10);
DECLARE@RequestIDNVARCHAR(10);
DECLARE@result111NVARCHAR(MAX);
--游标遍历临时表
DECLAREcurCURSORFOR
SELECT
CAST(SPIDASNVARCHAR),
Status,
Login,
HostName,
BlkBy,
DBName,
Command,
CAST(CPUTimeASNVARCHAR),
CAST(DiskIOASNVARCHAR),
LastBatch,
ProgramName,
CAST(SPID2ASNVARCHAR),
CAST(RequestIDASNVARCHAR)
FROM#Who2;
OPENcur;
FETCHNEXTFROMcurINTO@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;
FETCHNEXTFROMcurINTO@SPID1,@Status11,@Login,@HostName,@BlkBy,@DBName,@Command,@CPUTime,@DiskIO,@LastBatch,@ProgramName,@SPID2,@RequestID;
END
CLOSEcur;
DEALLOCATEcur;
--删除临时表
DROPTABLE#Who2;
SETNOCOUNTOFF;
print'17.活动用户和进程的信息'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
execsp_who'active'
SETNOCOUNTOFF;
print'18.查看所有数据库用户所属的角色信息'
print'----------------------------'
print'*********************************'
execsp_helpsrvrolemember
SETNOCOUNTON;
--创建用于存储sp_helpsrvrolemember结果的临时表
CREATETABLE#SrvRoleMember(
ServerRoleNVARCHAR(255),
MemberNameNVARCHAR(255),
MemberSIDVARBINARY(MAX)
);
--将sp_helpsrvrolemember的结果插入到临时表中
INSERTINTO#SrvRoleMember(ServerRole,MemberName,MemberSID)
EXECsp_helpsrvrolemember;
--声明变量来存储每一行的结果
DECLARE@ServerRoleNVARCHAR(255);
DECLARE@MemberNameNVARCHAR(255);
DECLARE@MemberSIDNVARCHAR(MAX);
DECLARE@result99NVARCHAR(MAX);
--将MemberSID转换为十六进制字符串
DECLARE@HexMemberSIDNVARCHAR(MAX);
--游标遍历临时表
DECLAREcurCURSORFOR
SELECT
ServerRole,
MemberName,
CONVERT(NVARCHAR(MAX),MemberSID,1)ASMemberSID--使用样式1转换为十六进制字符串
FROM#SrvRoleMember;
OPENcur;
FETCHNEXTFROMcurINTO@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;
FETCHNEXTFROMcurINTO@ServerRole,@MemberName,@MemberSID;
END
CLOSEcur;
DEALLOCATEcur;
--删除临时表
DROPTABLE#SrvRoleMember;
SETNOCOUNTOFF;
print'19.查看链接服务器'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
execsp_helplinkedsrvlogin
SETNOCOUNTOFF;
print'20.查询文件组和文件'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
select
df.[name],df.physical_name,df.[size],df.growth,
f.[name][filegroup],f.is_default
fromsys.database_filesdfjoinsys.filegroupsf
ondf.data_space_id=f.data_space_id
Go
SETNOCOUNTON;
--创建用于存储查询结果的临时表
CREATETABLE#DatabaseFiles(
nameNVARCHAR(255),
physical_nameNVARCHAR(260),
sizeINT,
growthINT,
filegroupNVARCHAR(255),
is_defaultBIT
);
--将查询结果插入到临时表中
INSERTINTO#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
FROMsys.database_filesdf
JOINsys.filegroupsfONdf.data_space_id=f.data_space_id;
--声明变量来存储每一行的结果
DECLARE@nameNVARCHAR(255);
DECLARE@physical_nameNVARCHAR(260);
DECLARE@sizeNVARCHAR(10);
DECLARE@growthNVARCHAR(10);
DECLARE@filegroupNVARCHAR(255);
DECLARE@is_defaultNVARCHAR(5);
DECLARE@resultNVARCHAR(MAX);
--游标遍历临时表
DECLAREcurCURSORFOR
SELECT
name,
physical_name,
CAST(sizeASNVARCHAR(10)),
CAST(growthASNVARCHAR(10)),
filegroup,
CAST(is_defaultASNVARCHAR(5))
FROM#DatabaseFiles;
OPENcur;
FETCHNEXTFROMcurINTO@name,@physical_name,@size,@growth,@filegroup,@is_default;
WHILE@@FETCH_STATUS=0
BEGIN
--格式化并打印每一行的结果
SET@result='Name:'+ISNULL(@name,'')+','+
'PhysicalName:'+ISNULL(@physical_name,'')+','+
'Size:'+ISNULL(@size,'')+','+
'Growth:'+ISNULL(@growth,'')+','+
'Filegroup:'+ISNULL(@filegroup,'')+','+
'IsDefault:'+ISNULL(@is_default,'');
PRINT@result;
FETCHNEXTFROMcurINTO@name,@physical_name,@size,@growth,@filegroup,@is_default;
END
CLOSEcur;
DEALLOCATEcur;
--删除临时表
DROPTABLE#DatabaseFiles;
SETNOCOUNTOFF;
print'21.查看SQLServer的实际内存占用'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
select*fromsysperfinfowherecounter_namelike'%Memory%'
--声明变量
DECLARE@counter_nameNVARCHAR(128);
DECLARE@instance_nameNVARCHAR(128);
DECLARE@cntr_valueBIGINT;
DECLARE@rowNVARCHAR(MAX);
--声明游标
DECLAREmemory_cursorCURSORFOR
SELECTcounter_name,instance_name,cntr_value
FROMsys.dm_os_performance_counters
WHEREcounter_nameLIKE'%Memory%';
--打开游标
OPENmemory_cursor;
--获取第一行数据
FETCHNEXTFROMmemory_cursorINTO@counter_name,@instance_name,@cntr_value;
--打印列名
PRINT'CounterName|InstanceName|CounterValue';
--遍历游标中的数据
WHILE@@FETCH_STATUS=0
BEGIN
--拼接每一行数据
SET@row=LEFT(@counter_name+SPACE(20),20)+'|'
+LEFT(ISNULL(@instance_name,'N/A')+SPACE(20),20)+'|'
+CAST(@cntr_valueASNVARCHAR);
--打印当前行数据
PRINT@row;
--获取下一行数据
FETCHNEXTFROMmemory_cursorINTO@counter_name,@instance_name,@cntr_value;
END
--关闭游标
CLOSEmemory_cursor;
--释放游标
DEALLOCATEmemory_cursor;
SETNOCOUNTOFF;
print'22.显示所有数据库的日志空间信息'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
dbccsqlperf(logspace)
Go
--创建一个临时表来存储DBCCSQLPERF(LOGSPACE)的结果
CREATETABLE#LogSpace(
[DatabaseName]NVARCHAR(128),
[LogSize(MB)]FLOAT,
[LogSpaceUsed(%)]FLOAT,
[Status]INT
);
--插入DBCCSQLPERF(LOGSPACE)的结果到临时表
INSERTINTO#LogSpace
EXEC('DBCCSQLPERF(LOGSPACE)');
--声明变量
DECLARE@DatabaseNameNVARCHAR(128);
DECLARE@LogSizeMBFLOAT;
DECLARE@LogSpaceUsedPercentFLOAT;
DECLARE@StatusINT;
DECLARE@rowNVARCHAR(MAX);
--声明游标
DECLARElogspace_cursorCURSORFOR
SELECT[DatabaseName],[LogSize(MB)],[LogSpaceUsed(%)],[Status]
FROM#LogSpace;
--打开游标
OPENlogspace_cursor;
--获取第一行数据
FETCHNEXTFROMlogspace_cursorINTO@DatabaseName,@LogSizeMB,@LogSpaceUsedPercent,@Status;
--打印列名
PRINT'DatabaseName|LogSize(MB)|LogSpaceUsed(%)|Status';
--遍历游标中的数据
WHILE@@FETCH_STATUS=0
BEGIN
--拼接每一行数据，并保证对齐
SET@row=LEFT(@DatabaseName+SPACE(25),25)+'|'
+RIGHT(SPACE(20)+CAST(@LogSizeMBASNVARCHAR),20)+'|'
+RIGHT(SPACE(25)+CAST(@LogSpaceUsedPercentASNVARCHAR),25)+'|'
+CAST(@StatusASNVARCHAR);
--打印当前行数据
PRINT@row;
--获取下一行数据
FETCHNEXTFROMlogspace_cursorINTO@DatabaseName,@LogSizeMB,@LogSpaceUsedPercent,@Status;
END
--关闭游标
CLOSElogspace_cursor;
--释放游标
DEALLOCATElogspace_cursor;
--删除临时表
DROPTABLE#LogSpace;
select*,CAST(cntr_value/1024.0asdecimal(20,1))MemoryMB
frommaster.sys.sysperfinfo
wherecounter_name='TotalServerMemory(KB)'
SETNOCOUNTOFF;
print'23.查询表空间的已使用大小'
print'----------------------------'
print'*********************************'
SETNOCOUNTON;
SELECT
DB_NAME()ASDatabaseName,
mf.nameASFileName,
mf.size*8/1024ASSizeMB,
mf.size*8/1024-FILEPROPERTY(mf.name,'SpaceUsed')*8/1024ASFreeSpaceMB,
FILEPROPERTY(mf.name,'SpaceUsed')*8/1024ASUsedSpaceMB
FROM
sys.master_filesmf
WHERE
mf.database_id=DB_ID()
Go
--创建一个临时表来存储查询结果
CREATETABLE#FileSpace(
DatabaseNameNVARCHAR(128),
FileNameNVARCHAR(128),
SizeMBDECIMAL(18,2),
FreeSpaceMBDECIMAL(18,2),
UsedSpaceMBDECIMAL(18,2)
);
--插入查询结果到临时表
INSERTINTO#FileSpace
SELECT
DB_NAME()ASDatabaseName,
mf.nameASFileName,
mf.size*8/1024ASSizeMB,
mf.size*8/1024-FILEPROPERTY(mf.name,'SpaceUsed')*8/1024ASFreeSpaceMB,
FILEPROPERTY(mf.name,'SpaceUsed')*8/1024ASUsedSpaceMB
FROM
sys.master_filesmf
WHERE
mf.database_id=DB_ID();
--声明变量
DECLARE@DatabaseNameNVARCHAR(128);
DECLARE@FileNameNVARCHAR(128);
DECLARE@SizeMBDECIMAL(18,2);
DECLARE@FreeSpaceMBDECIMAL(18,2);
DECLARE@UsedSpaceMBDECIMAL(18,2);
DECLARE@rowNVARCHAR(MAX);
--声明游标
DECLAREfile_cursorCURSORFOR
SELECTDatabaseName,FileName,SizeMB,FreeSpaceMB,UsedSpaceMB
FROM#FileSpace;
--打开游标
OPENfile_cursor;
--获取第一行数据
FETCHNEXTFROMfile_cursorINTO@DatabaseName,@FileName,@SizeMB,@FreeSpaceMB,@UsedSpaceMB;
--打印列名
PRINT'DatabaseName|FileName|Size(MB)|FreeSpace(MB)|UsedSpace(MB)';
--遍历游标中的数据
WHILE@@FETCH_STATUS=0
BEGIN
--拼接每一行数据，并保证对齐
SET@row=LEFT(@DatabaseName+SPACE(20),20)+'|'
+LEFT(@FileName+SPACE(25),25)+'|'
+RIGHT(SPACE(15)+CAST(@SizeMBASNVARCHAR(15)),15)+'|'
+RIGHT(SPACE(18)+CAST(@FreeSpaceMBASNVARCHAR(18)),18)+'|'
+RIGHT(SPACE(15)+CAST(@UsedSpaceMBASNVARCHAR(15)),15);
--打印当前行数据
PRINT@row;
--获取下一行数据
FETCHNEXTFROMfile_cursorINTO@DatabaseName,@FileName,@SizeMB,@FreeSpaceMB,@UsedSpaceMB;
END
--关闭游标
CLOSEfile_cursor;
--释放游标
DEALLOCATEfile_cursor;
--删除临时表
DROPTABLE#FileSpace;
SETNOCOUNTOFF;
print'----------------------------'
print'结束巡检'