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'結束巡檢'