Sqlserver 清除维护相关日志

在Sqlserver使用过程中msdb数据库越来越大 这个就要定期删除长时间不用的日志。

---1.查看作业历史记录

SELECT TOP 1000 [instance_id]
      ,[job_id]
      ,[step_id]
      ,[step_name]
      ,[sql_message_id]
      ,[sql_severity]
      ,[message]
      ,[run_status]
      ,[run_date]
      ,[run_time]
      ,[run_duration]
      ,[operator_id_emailed]
      ,[operator_id_netsent]
      ,[operator_id_paged]
      ,[retries_attempted]
      ,[server]
  FROM [msdb].[dbo].[sysjobhistory]
 
 --删除操作记录
  delete from [msdb].[dbo].[sysjobhistory]

 

 

---2.查看维护计划日志

--查询指定日期前的日志
SELECT [task_detail_id]
      ,[plan_id]
      ,[subplan_id]
      ,[start_time]
      ,[end_time]
      ,[succeeded]
      ,[logged_remotely]
      ,[source_server_name]
      ,[plan_name]
      ,[subplan_name]
  FROM [msdb].[dbo].[sysmaintplan_log] where start_time<'2021-01-01'
-- 删除指定日期前的日志
  delete from [msdb].[dbo].[sysmaintplan_log]  where start_time<'2021-01-01'

---3.查看维护计划日志详情

--查询指定日期前的日志明细
SELECT  [task_detail_id]
      ,[line1]
      ,[line2]
      ,[line3]
      ,[line4]
      ,[line5]
      ,[server_name]
      ,[start_time]
      ,[end_time]
      ,[error_number]
      ,[error_message]
      ,[command]
      ,[succeeded]
  FROM [msdb].[dbo].[sysmaintplan_logdetail] where start_time<'2021-01-01'
 
--删除指定日期前的日志明细 
  delete from [msdb].[dbo].[sysmaintplan_logdetail] where start_time<'2021-01-01'

 

USE msdb

GO

-- 查看对象空间专用情况，发现排名第一的sysmaintplan_logdetail占用空间超过100G

SELECT  OBJECT_NAME(i.object_id) AS objectName,

        i.name AS indexName,
        SUM(a.total_pages) AS totalPages,
        SUM(a.used_pages) AS usedPages,
        SUM(a.data_pages) AS dataPages,
        (SUM(a.total_pages) * 8) / 1024 AS totalSpaceMB,
        (SUM(a.used_pages) * 8) / 1024 AS usedSpaceMB,
        (SUM(a.data_pages) * 8) / 1024 AS dataSpaceMB
FROM    sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND
                               i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY i.object_id,
        i.index_id,
        i.name
ORDER BY SUM(a.total_pages) DESC,
        OBJECT_NAME(i.object_id);

-- 清理维护日志（truncate之前，临时解除外键约束）
ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
GO
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];
GO
TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;
GO
TRUNCATE TABLE msdb.dbo.sysmaintplan_log;
GO

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])
REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);
GO
ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])
REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;
GO