SQLServer巡檢指令碼

上半年的巡檢任務基本結束，客戶的oracle資料庫安裝在AIX上的，Linux上的，Windows上的各式各樣也都見識過了。最近公司新談了一個客戶，有8套生產資料庫，除了Oracle外還有mysql和sqlserver。過去到是使用過SQLServer，但是僅限於開發程式的角度，此次要做健康型檢查了，總得準備一下SQLServer的指令碼並出個報告吧。翻遍論壇的各個角落湊上了一篇備用，等巡檢回來再做總結。

--1.檢視資料庫版本資訊
select @@version
--2.檢視所有資料庫名稱及大小
exec sp_helpdb
--3.檢視資料庫所在機器的作業系統引數
exec master..xp_msver
--4.檢視資料庫啟動的引數
exec sp_configure
--5.檢視資料庫啟動時間
select convert(varchar(30),login_time,120)
from master..sysprocesses where spid=1
--6.檢視資料庫伺服器名
select 'Server Name:'+ltrim(@@servername)
--7.檢視資料庫例項名
select 'Instance:'+ltrim(@@servicename)
--8.資料庫的磁碟空間呢使用資訊
exec sp_spaceused
--9.日誌檔案大小及使用情況
dbcc sqlperf(logspace)
--10.表的磁碟空間使用資訊
exec sp_spaceused 'tablename'
--11.獲取磁碟讀寫情況
select
@@total_read [讀取磁碟次數],
@@total_write [寫入磁碟次數],
@@total_errors [磁碟寫入錯誤數],
getdate() [當前時間]
--12.獲取I/O工作情況
select @@io_busy,
@@timeticks [每個時鐘週期對應的微秒數],
@@io_busy*@@timeticks [I/O操作毫秒數],
getdate() [當前時間]
--13.檢視CPU活動及工作情況
select
@@cpu_busy,
@@timeticks [每個時鐘週期對應的微秒數],
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作時間(秒)],
@@idle*cast(@@timeticks as float)/1000 [CPU空閒時間(秒)],
getdate() [當前時間]
--14.檢查鎖與等待
exec sp_lock
--15.檢查死鎖
exec sp_who_lock --自己寫個儲存過程即可
/*
create procedure sp_who_lock
as
begin
    declare @spid int,@bl int,
    @intTransactionCountOnEntry int,
    @intRowcount int,
    @intCountProperties int,
    @intCounter int
    create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
    IF @@ERROR<>0 RETURN @@ERROR
    insert into #tmp_lock_who(spid,bl) select 0 ,blocked
    from (select * from sys.sysprocesses where blocked>0 ) a
    where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b
    where a.blocked=spid)
    union select spid,blocked from sys.sysprocesses where blocked>0
    IF @@ERROR<>0 RETURN @@ERROR
        -- 找到臨時表的記錄數
        select @intCountProperties = Count(*),@intCounter = 1
        from #tmp_lock_who
    IF @@ERROR<>0 RETURN @@ERROR
    if @intCountProperties=0
    select '現在沒有阻塞和死鎖資訊' as message
    -- 迴圈開始
    while @intCounter <= @intCountProperties
    begin
    -- 取第一條記錄
    select @spid = spid,@bl = bl
    from #tmp_lock_who where id = @intCounter
    begin
    if @spid =0
        select '引起資料庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '程式號,其執行的SQL語法如下'
    else
        select '程式號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '程式號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前程式執行的SQL語法如下'
    DBCC INPUTBUFFER (@bl )
    end
    -- 迴圈指標下移
    set @intCounter = @intCounter + 1
    end
    drop table #tmp_lock_who
    return 0
end
*/

--16.使用者和程式資訊
exec sp_who
exec sp_who2

--17.活動使用者和程式的資訊
exec sp_who 'active'

--18.檢視程式中正在執行的SQL
dbcc inputbuffer(程式號)
exec sp_who3
/*
CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )
AS
    BEGIN


        SELECT  SPID = er.session_id ,
                Status = ses.status ,
                [Login] = ses.login_name ,
                Host = ses.host_name ,
                BlkBy = er.blocking_session_id ,
                DBName = DB_NAME(er.database_id) ,
                CommandType = er.command ,
                SQLStatement = st.text ,
                ObjectName = OBJECT_NAME(st.objectid) ,
                ElapsedMS = er.total_elapsed_time ,
                CPUTime = er.cpu_time ,
                IOReads = er.logical_reads + er.reads ,
                IOWrites = er.writes ,
                LastWaitType = er.last_wait_type ,
                StartTime = er.start_time ,
                Protocol = con.net_transport ,
                ConnectionWrites = con.num_writes ,
                ConnectionReads = con.num_reads ,
                ClientAddress = con.client_net_address ,
                Authentication = con.auth_scheme
        FROM    sys.dm_exec_requests er
                OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
                LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
                LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
        WHERE   er.session_id > 50
                AND @SessionID IS NULL
                OR er.session_id = @SessionID
        ORDER BY er.blocking_session_id DESC ,
                er.session_id


    END
*/

--19.檢視所有資料庫使用者登入資訊
exec sp_helplogins

--20.檢視所有資料庫使用者所屬的角色資訊
exec sp_helpsrvrolemember

--21.檢視連結伺服器
exec sp_helplinkedsrvlogin

--22.檢視遠端資料庫使用者登入資訊
exec sp_helpremotelogin

--23.獲取網路資料包統計資訊
select
@@pack_received [輸入資料包數量],
@@pack_sent [輸出資料包數量],
@@packet_errors [錯誤包數量],
getdate() [當前時間]

--24.檢查資料庫中的所有物件的分配和機構完整性是否存在錯誤
dbcc checkdb

--25.查詢檔案組和檔案
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

--26.檢視資料庫中所有表的條數
select  b.name as tablename ,
        a.rowcnt as datacount
from    sysindexes a ,
        sysobjects b
where   a.id = b.id
        and a.indid < 2
        and objectproperty(b.id, 'IsMSShipped') = 0

--27.得到最耗時的前10條T-SQL語句
;with maco as
(
    select top 10
        plan_handle,
        sum(total_worker_time) as total_worker_time ,
        sum(execution_count) as execution_count ,
        count(1) as sql_count
    from sys.dm_exec_query_stats group by plan_handle
    order by sum(total_worker_time) desc
)
select  t.text ,
        a.total_worker_time ,
        a.execution_count ,
        a.sql_count
from    maco a
        cross apply sys.dm_exec_sql_text(plan_handle) t

--28. 檢視SQL Server的實際記憶體佔用
select * from sysperfinfo where counter_name like '%Memory%'


--29.顯示所有資料庫的日誌空間資訊
dbcc sqlperf(logspace)

--30.收縮資料庫
dbcc shrinkdatabase(databaseName)