sqlserver的日常检查脚本归结

核心提示：select @backupsize = backup_size from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @db

select @backupsize = backup_size from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')

select @filepath = physical_device_name from msdb..backupmediafamily where media_set_id = @media_set_id

exec master..xp_fileexist @filepath , @filestatus out

if @filestatus = 1

set @fileavailable = 'available'

else

set @fileavailable = 'not available'

if (datediff(day,@db_bkpdate,getdate()) > 7)

set @status = 'warning'

else

set @status = 'healthy'

set @backupsize = (@backupsize/1024)/1024

insert into #backup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable

update #backup_details

set status = 'warning' where bkpdate is null

set @counter = @counter - 1

end

select substring(servername,0,20) AS [服务器名],

substring(databasename,0,20) AS [数据库名], 

rtrim(ltrim(bkpdate)) AS  [备份日期],

rtrim(ltrim(backupsize_in_mb)) AS [备份大小],

rtrim(ltrim([status])) AS [备份状态],

substring(rtrim(ltrim(filepath)),0,40) AS  [备份文件路径],

rtrim(fileavailable) AS  [备份文件是否可用]

 from #backup_details where databasename not in ('tempdb','northwind','pubs')

drop table #backup_details

set nocount off

go

print '*********************************'

print '                             '

print '                             '

print '                             '

print '-------------------------------------'

print '16.监控CPU瓶颈'

print '-------------------------------------'

print '*********************************'

use master

go

print '-------------------------------------'

print '16.1当前缓存的哪些批处理或过程占用了大部分 CPU 资源'

print '-------------------------------------'

SELECT TOP 50 

      ltrim(SUM(qs.total_worker_time)) AS total_cpu_time, 

      ltrim(SUM(qs.execution_count)) AS total_execution_count,

      ltrim(COUNT(*)) AS  number_of_statements, 

      qs.sql_handle 

FROM sys.dm_exec_query_stats AS qs

GROUP BY qs.sql_handle

ORDER BY SUM(qs.total_worker_time) DESC

go

print '                             '