SQLSERVER处理distribution数据库

最新推荐文章于 2024-03-12 11:34:56 发布

qq_42967961

最新推荐文章于 2024-03-12 11:34:56 发布

阅读量728

点赞数 1

分类专栏： SQL Server

本文链接：https://blog.csdn.net/qq_42967961/article/details/116111489

版权

SQL Server 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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'