SQL Server 维护脚本分享（13）数据库内部日常巡检

--磁盘空间可以情况EXEC master.dbo.xp_fixeddrivesgo --事务日志使用率DBCC sqlperf(logspace)go--检查数据库是否正常select name,state_desc,log_reuse_wait_desc,user_access_desc,recovery_model_desc,is_cdc_enabled,compatibility_level,collation_namefrom sys.databases--where state_desc<>'ONLINE' or user_access_desc<>'MULTI_USER'go--查看 MSSQL 错误日志大小EXEC xp_enumerrorlogs 1-- sqlserver错误日志EXEC xp_enumerrorlogs 2-- 代理日志go--检查数据库(错误日志)是否发生错误DECLARE @SQL VARCHAR(200)='xp_readerrorlog 0,1,N''ERROR'',N'''','''+CONVERT(VARCHAR(10),DATEADD(D,-1,GETDATE()),120)+''','''',''DESC''';EXEC(@SQL)go--查看最近失败的代理作业SELECT TOP 10 run_date,run_time,run_duration,step_name,messageFROM  msdb..sysjobhistory WHERE run_status = 0ORDER BY run_date DESC,run_time DESCgo--数据库最近备份情况SELECT database_name ,MAX(CASE WHEN type='D' THEN backup_finish_date ELSE NULL END) AS 完整备份时间 ,MAX(CASE WHEN type='I' THEN backup_finish_date ELSE NULL END) AS 差异备份时间 ,MAX(CASE WHEN type='L' THEN backup_finish_date ELSE NULL END) AS 日志备份时间 FROM( SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date  FROM msdb.dbo.backupset GROUP BY database_name,type ) T GROUP BY database_name go--各数据库连接数SELECT @@ServerName AS server ,NAME AS dbname ,COUNT(STATUS) AS number_of_connections ,GETDATE() AS timestampFROM sys.databases sdLEFT JOIN sysprocesses sp ON sd.database_id = sp.dbidWHERE database_id NOT BETWEEN 1 AND 4GROUP BY NAMEGO--是否有未提交事物select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran ,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s where open_tran <> 0 go--是否堵塞SELECT spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,textFROM master.dbo.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) sWHERE blocked > 0 OR spid IN(SELECT blocked FROM master.dbo.sysprocesses WHERE blocked > 0)ORDER BY cmdgo--SELECT * FROM ::fn_trace_gettable((select [path] from sys.traces where id=1), 0)  ---------------------------------------------------------------------------------------------复制相关---------------------------------------------------------------------------------------------事务复制：未分发命令数(分发服务器执行)SELECT  'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N'''           + a.publisher + ''', @publisher_db = N''' + a.publisher_db           + ''', @publication = N''' + a.publication + ''', @subscriber = N'''           + c.name + ''', @subscriber_db = N''' + b.subscriber_db           + ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR)   FROM    distribution.dbo.MSreplication_monitordata a ( NOLOCK )   INNER JOIN (       SELECT   publication_id ,subscriber_id ,subscriber_db ,subscription_type       FROM     distribution.dbo.MSsubscriptions (NOLOCK)       GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type   ) b ON a.publication_id = b.publication_id   INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id   WHERE   a.agent_type = 1go--查看前10个等待分发命令最多的事务数 及 查看命令use distribution go SELECT top 10  A.xact_seqno,A.entry_time,COUNT(*) AS cmds FROM distribution.dbo.MSrepl_transactions A(NOLOCK)  INNER JOIN distribution.dbo.MSrepl_commands B(NOLOCK)  ON A.xact_seqno=B.xact_seqno GROUP BY A.xact_seqno,A.entry_time ORDER BY cmds DESC go--查看出现错误的事务序列号(历史记录) (分发服务器执行)SELECT  'EXEC distribution.dbo.sp_helpsubscriptionerrors N''' + a.publisher + ''', N''' + a.publisher_db    + ''', N''' + a.publication + ''', N'''   + c.name + ''',N''' + b.subscriber_db    + ''''FROM    distribution.dbo.MSreplication_monitordata a ( NOLOCK )   INNER JOIN (       SELECT   publication_id ,subscriber_id ,subscriber_db ,subscription_type       FROM     distribution.dbo.MSsubscriptions (NOLOCK)       GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type   ) b ON a.publication_id = b.publication_id   INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id   WHERE   a.agent_type = 1GO