在创建发布时,如果选择立即初始化,会将immediate_sync属性设置为true。
如果immediate_sync属性为true时,snapshot文件和发布事务及发布命令将一直保留到指定的事务保持期后才会被删除。
事务保持期设置:
在同一个DB上,如果有一个发布的属性被设置为true,则该DB上其他发布产生的事务也会被保留至事务保持期
查看immediate_sync属性为true
的发布
USE distribution;
GO
SELECT
p.publication,
p.publisher_db,
ser.name AS publisherServer
FROM
MSpublications p
INNER JOIN sys.servers ser
ON p.publisher_id
=ser.server_id
WHERE immediate_sync=1
AND publication_type
=0
修改immediate_sync属性
exec sp_changepublication
@publication
= ‘publicationName‘ ,
@property = ‘allow_anonymous‘ ,
@value =
false
exec sp_changepublication
@publication = ‘publicationName‘ ,
@property = ‘immediate_sync‘ ,
@value =
false
某些情况下,immediate_sync属性被设置为false,发布事务仍被保留,需查看
SELECT
*
FROM distribution.dbo.MSdistribution_agents
WHERE
subscriber_id<0
如果存在记录,则需要手动清理部分数据(小心使用)
SELECT *
INTO
MSdistribution_agents_bak
FROM MSdistribution_agents
WHERE
subscriber_id<0
--============================================
SELECT
*
INTO MSdistribution_history_bak
FROM MSdistribution_history
WHERE
agent_id IN
(
SELECT id
FROM MSdistribution_agents
WHERE
subscriber_id<0
)
--============================================
DELETE
FROM
MSdistribution_history
WHERE agent_id IN
(
SELECT id
FROM
MSdistribution_agents
WHERE subscriber_id<0
)
--============================================
DELETE FROM
MSdistribution_agents
WHERE subscriber_id<0
参考:http://www.cnblogs.com/stswordman/archive/2013/08/19/3267688.html