SQL Server 数据库巡检脚本.docxVIP

--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?@@ERROR0?RETURN?@@ERROR????????insert?into?#tmp_lock_who(spid,bl)?select?0?,blocked????????from?(select?*?from?sys.sysprocesses?where?blocked0?)?a?????????where?not?exists(select?*?from?(select?*?from?sys.sysprocesses?where?blocked0?)?b?????????where?a.blocked=spid)????????union?select?spid,blocked?from?sys.sysprocesses?where?blocked0????????IF?@@ERROR0?RETURN?@@ERROR????????????--?找到临时表的记录数????????????select?@intCountProperties?=?Count(*),@intCounter?=?1????????????from?#tmp_lock_who????????IF?@@ERROR0?RETURN?@@ERROR????????if?@intCountProperties=0????????select?现在没有阻塞和死锁信息?as?message????????--?循环开始????????while?@intCounter?=?@intCountProperties????????begin????????--?取第一条记录????????select?@spid?=?spid,@bl?=?bl?