SQL Server 数据库巡检脚本 [转载]

--1.查看数据库版本信息 

select @@version 

--2.查看所有数据库名称及大小 

select sp_helpdb 

--3.查看数据库所在机器的操作系统参数 

exec master..xp_msver 

--4.查看数据库启动的参数 

exec sp_configure 

--5.查看数据库启动时间 

selectconvert(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 asfloat)/1000 [CPU工作时间(秒)], 

@@idle*cast(@@timeticks asfloat)/1000 [CPU空闲时间(秒)], 

getdate() [当前时间] 

--14.检查锁与等待 

exec sp_lock 

--15.检查死锁 

exec sp_who_lock --自己写个存储过程即可 

/* 

createprocedure sp_who_lock 

as 

begin 

    declare @spid int,@bl int, 

    @intTransactionCountOnEntry int, 

    @intRowcount int, 

    @intCountProperties int, 

    @intCounter int 

    createtable #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint) 

    IF @@ERROR<>0 RETURN @@ERROR 

    insertinto #tmp_lock_who(spid,bl) select 0 ,blocked 

    from (select * from sysprocesses where blocked>0 ) a  

    wherenot exists(select * from (select * from sysprocesses where blocked>0 ) b  

    where a.blocked=spid) 

    unionselect spid,blocked from 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 ASVARCHAR(10)) + '进程号,其执行的SQL语法如下' 

    else 

        select'进程号SPID：'+ CAST(@spid ASVARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl ASVARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' 

    DBCC INPUTBUFFER (@bl ) 

    end 

    -- 循环指针下移 

    set @intCounter = @intCounter + 1 

    end 

    droptable #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 

 

--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.nameas 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    

(      

    selecttop 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 groupby plan_handle   

    orderbysum(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) 

转载于:https://www.cnblogs.com/worfdream/articles/2986446.html