SQL Server上部署的数据库比较多,MSDBData达到130GB,分析到最后是由于维护计划历史记录太多导致的,
分析步骤记录如下:
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