SQLSERVER处理distribution数据库

USE [distribution]
GO
DBCC SHRINKFILE (N'distribution' , 50)
GO




SELECT     t.name AS TableName
          ,p.rows AS RowCounts
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
WHERE      t.name IN ('MSrepl_commands', 'MSrepl_transactions')

循环删除MSrepl_conmmands表过期数据

SET ROWCOUNT 0

WHILE 1 = 1
BEGIN
delete TOP(1000000) a 
    from distribution.dbo.MSrepl_commands a with(nolock)
    inner join distribution.dbo.MSrepl_transactions b with(nolock)
        on a.publisher_database_id=b.publisher_database_id  
        and a.xact_seqno=b.xact_seqno
        where entry_time<'2021-04-22';
IF @@rowcount < 1000000 or  getdate() > '2021-04-23 6:00:00'
BREAK;
END

查询表量

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







select  count(1)
    from distribution.dbo.MSrepl_commands a with(nolock)
    inner join distribution.dbo.MSrepl_transactions b with(nolock)
        on a.publisher_database_id=b.publisher_database_id  
        and a.xact_seqno=b.xact_seqno
        where entry_time<'2021-04-20 07:01:00'


delete TOP(2000000) a 
    from distribution.dbo.MSrepl_commands a with(nolock)
    inner join distribution.dbo.MSrepl_transactions b with(nolock)
        on a.publisher_database_id=b.publisher_database_id  
        and a.xact_seqno=b.xact_seqno
        where entry_time<'2021-04-21'