sqlserver的日常检查脚本归结

核心提示：SELECT 作业名 = sj.name ,开始时间 = sja.start_execution_date ,结束时间 = sja.stop_execution_date ,状态 = CASE WHEN ISNULL(sjh.

SELECT 作业名     = sj.name 

      ,开始时间 = sja.start_execution_date 

      ,结束时间   = sja.stop_execution_date 

      ,状态    = CASE  

                   WHEN ISNULL(sjh.run_status,-1) = -1 AND sja.start_execution_date IS NULL AND sja.stop_execution_date IS NULL THEN 'Idle' 

                   WHEN ISNULL(sjh.run_status,-1) = -1 AND sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running' 

                   WHEN ISNULL(sjh.run_status,-1) =0  THEN 'Failed' 

                   WHEN ISNULL(sjh.run_status,-1) =1  THEN 'Succeeded' 

                   WHEN ISNULL(sjh.run_status,-1) =2  THEN 'Retry' 

                   WHEN ISNULL(sjh.run_status,-1) =3  THEN 'Canceled' 

                   END 

  FROM MSDB.DBO.sysjobs sj 

  JOIN MSDB.DBO.sysjobactivity sja 

    ON sj.job_id = sja.job_id  

  JOIN (SELECT MaxSessionid = MAX(Session_id) FROM MSDB.DBO.syssessions) ss 

    ON ss.MaxSessionid = sja.session_id 

LEFT JOIN MSDB.DBO.sysjobhistory sjh 

    ON sjh.instance_id = sja.job_history_id

print '                             '

print '                             '

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

print '27. 获得每个数据库空间使用情况'

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

CREATE TABLE #output( 

server_name varchar(128), 

dbname varchar(128), 

physical_name varchar(260), 

dt datetime, 

file_group_name varchar(128), 

size_mb int, 

free_mb int)  

exec sp_MSforeachdb @command1= 

'USE [?]; INSERT #output 

SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server_name, 

''?'' AS dbname, 

f.filename AS physical_name, 

CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt, 

g.groupname, 

CAST (size*8.0/1024.0 AS int) AS ''size_mb'', 

CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS ''free_mb''