SQL_Server_数据库巡检脚本.docx

-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.获取磁盘读写情况selecttotal_read 读取磁盘次数,total_write 写入磁盘次数,total_errors 磁盘写入错误数,getdate() 当前时间-12.获取I/O工作情况select io_busy,timeticks 每个时钟周期对应的微秒数,io_busy*timeticks I/O操作毫秒数,getdate() 当前时间-13.查看CPU活动及工作情况selectcpu_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_lockasbegin 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 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.获取网络数据包统计信息selectpack_received 输入数据包数量,pack_sent 输出数据包数量,packet_errors 错误包数量,getdate() 当前时间-24.检查数据库中的所有对象的分配和机构完整性是否存在错误dbcc checkdb-25.查询文件组和文件select ,df.physical_name,df.size,df.growth, filegroup,f.is_defaultfrom sys.database_files df join sys.filegroups fon df.data_space_id = f.data_space_id-26.查看数据库中所有表的条数select as tablename , a.rowcnt as datacountfrom sysindexes a , sysobjects bwhere 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_countfrom maco a cross apply sys.dm_exec_sql_text(plan_handle) t-28. 查看SQL Server的实际内