ITPub博客

首页 > 数据库 > SQL Server > Sqlserver distribution数据库文件大,清理MSrepl_commands表后恢复正常

Sqlserver distribution数据库文件大,清理MSrepl_commands表后恢复正常

原创 SQL Server 作者:lusklusklusk 时间:2021-02-02 13:07:14 0 删除 编辑

官方文档




 


巡检发现distribution库的数据文件暴大,其中表MSrepl_commands占用了近100GB文件大小,该表是会被自动清理的,清理的job就是"Distribution clean up: distribution",检查下来发现该job正常运行,该job的代码就是 use distribution;EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72;自动清理72小时前的数据,推测可能是最近72小时内产生了太多的数据,手工执行清理36小时前的数据,use distribution;EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36;手工清理后表MSrepl_commands的数据减少到了10GB,distribution库的数据文件大小正常了



查询distribution下面哪些表占用空间的sql语句

use distribution;
GO
select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts
from (
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
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
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
    t.Name, s.Name, p.Rows) a
GROUP BY  a.tablename,a.SCHEMANAME
order by sum(a.TotalSpaceMB) desc


清理前



执行清理,use distribution;EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36

清理后


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2755346/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    438
  • 访问量
    791941