mssql sqlserver 安全检查—日常维护


摘要:
下文讲述mssql sqlserver安全检查中-例行维护任务涉及的事项,如下所示:



一、例行检查-日常维护任务涉及事项:

1.1 数据备份
1.2 完整性检查
1.3 索引维护
1.4 统计更新
常规情况下,我们会将数据库的日常维护任务全部放入数据库作业中自动运行,我们通过监控作业的运行情况,观察日常维护任务是否得到正常的运行


下文将分别举例介绍常用的数据脚本:

  -----此脚本可监控,数据库的所有作业运行历史(包括:数据维护计划 索引维护计划 ...)
  SELECT
  [j].[name] AS [作业名称],
  [h].[step_id] AS [步骤ID],
  [h].[step_name] AS [步骤名称],
  CONVERT(CHAR(10), CAST(STR([h].[run_date],8, 0) AS DATETIME), 121) AS [运行时间],
  STUFF(STUFF(RIGHT('000000' + CAST ( [h].[run_time] AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') 
    AS [RunTime],
  (([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100 + 31 ) / 60) 
    AS [运行耗时],
  CASE [h].[run_status] 
    WHEN 0 THEN '失败'
    WHEN 1 THEN '成功' 
    WHEN 2 THEN '重试' 
    WHEN 3 THEN '取消' 
    WHEN 4 THEN '运行中' 
  END AS [作业状态],
  [h].[message] AS [作业信息]  
FROM [msdb].[dbo].[sysjobhistory] [h]
INNER JOIN [msdb].[dbo].[sysjobs] [j] 
ON [h].[job_id] = [j].[job_id]
WHERE   [step_id] = 0
ORDER BY [运行时间];