镜像切换Logreader Agent报错：分发数据库中可能存在不一致的状态
软件环境：Windows Server 2008 R2 sp1 SQL Server 2008 R2 sp2
架构：ServerA(主体）+ServerB(镜像)+ServerSub(订阅)+ServerDist(分发)
特别注意：ServerDist分发服务器为单机，故开启了Sync_With_Backup选项。
关于Sync_With_Backup：
初衷：简单来说，开启该选项，使得单机分发库在宕机时，利用最近的系统数据库和用户数据库备份，还原到一台新的服务器上，即可使复制继续工作成为可能。
需要注意的是：在分发库开启该选项，意味着发布库的日志只有在分发库被备份后才会截断。
查看该选项是否开启：
SELECT name,is_sync_with_backup FROM sys.databases WHERE name LIKE 'distribution%'
开启该选项：
sp_replicationdboption 'distribution','sync with backup','true'
场景：
在生产环境中，分发库备份为半小时一次。
将主体从ServerA切换至ServerB时，Logreader Agent报错：
直观上可以看到，Logreader 认为dist_backup_lsn和dist_last_sn不一致，导致其无法继续工作。下面我们实际去看一下这两者到底是否一致。
下面是Logreader、Distribution Agent工作的步骤列表，为了方便，我都贴出来：
Log Reader Agent (logread.exe) – Sequence of Steps
1.Calls sp_MSadd_LogReader_History to write to MSLogReader_History – “Starting Agent”
2.sp_MShelp_logreader_agentid – Obtain log reader agent specific information for that publication
3.sp_MShelp_profile – Obtains profile information for the Log Reader
4.MSadd_logreader_history to write MSlogreader_history – “Initializing”
5.sp_MSget_last_transaction – determine where the log reader agent left off reading the log.
6.Read the transaction log – sp_replcmds
7.Processes the returned commands from the sp_replcmds in batches by calling sp_MSadd_repl_commands
8.Marks this transaction as committed in distribution database by using sp_repldone procedure
9.Adjusts the identity range if necessary and if you are using Automatic Identity Range Management y calling sp_MSpub_adjust_identity
10.Calls sp_MSget_last_transaction to check the last transaction read and stored in MSReplication_transactions table
11.When all transactions are read, LogRead.exe calls sp_MSAdd_logreader_history and writes message to MSLogReader_history “1 transactions with 9 commands were delivered”
•Distribution Agent (distrib.exe) - Sequence of Steps
1.master.db.sp_msget_jobstate – get the status of the job (if it is already started)
2.sp_Msadd_distribution_history – MSDistribution_history – Starting agent
3.sp_MSSubscription_Status – whether subscription has expired or the snapshot is ready
4.sp_server_info- determines the collation
5.sp_mshelp_subscriber_info – retrieve subscriber information
6.sp_mshelp_subscription_agentid – determine the name of the distribution agent
7.sp_Msadd_distribution_history – Initializing message – Msrepl_distribution_history
8.sp_Msadd_distribution_history – Connecting to Subscriber - Msrepl_distribution_history
9.so_datatype_info – to determine the data type mapping necessary to create the tracking table necessary for the Distribution agent
10.sp_MScheck_subscribe on subscription database – verifies that SQL Server Agent account is in sysadmin and db_owner role in subscription database
11.sp_mscreate_sub_tables on subscriber in subscription database – creates MSSusbcription_agents and MSreplication_subscriptions tables
12.Sp_MSinit_Subscription_agent – updates the Subscription agent information on subscription database
13.Retrieves transaction_timestamp and subscription_guid to determine what Distribution agent has already replicated to the Subscriber. Transaction_timestamp correlates to xact_seqno column in MSReplication_transactions table in distribution database. All values large than the xact_seqno will be replicated
14.If we are doing initial sync, Distribution Agent calls sp_MSupdatelastsyncinfo which updates MSreplication_susbcriptions and MSSusbcription_agents table
15.Starts to retrieve all transactions and their corresponding commands from MSReplication_transactions and MSreplication_commands table where transaction_timestamp column in subscription database < xact_seqno column in MSreplication_transactions table. Applies the transaction using sp_MS_get_repl_commands procedure
16.Issues dynamic SQL to update the MSreplication_subscriptions table with the last delivered transaction ID
17.sp_MSDistribution_history to write the MSrepl_distribution_history table with status message “nn transaction(S) with nn command(s) were delivered”在步骤5中，我们得知，Logreader用存储过程sp_Msget_last_transaction来定位发布库日志中最后一个被写入到分发库中的事务，我们找到这个存储过程的源码：
CREATE PROCEDURE sys.sp_MSget_last_transaction
(
@publisher_id int = NULL,
@publisher_db sysname,
@publisher sysname = NULL,
@max_xact_seqno varbinary(16) = NULL output
,@for_truncate bit = 0
)
AS
begin
declare @publisher_database_id int
declare @max_xact_id varbinary(16)
declare @sync_bit int
declare @sync_with_backup bit
set nocount on
-- security check
-- only db_owner can execute this
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
SELECT @sync_bit = 32
if @publisher_id is NULL
select @publisher_id = srvid from master.dbo.sysservers where
UPPER(srvname) = UPPER(@publisher)
-- Get publisher database id.
SELECT @publisher_database_id = id from MSpublisher_databases where publisher_id = @publisher_id and
publisher_db = @publisher_db
if exists ( select * from master.dbo.sysdatabases where
name = db_name() and
category & @sync_bit = 0)
select @sync_with_backup = 0
else
select @sync_with_backup = 1
if @for_truncate = 0
begin
select top 1 @max_xact_id = rt.xact_id, @max_xact_seqno = rt.xact_seqno
from
MSrepl_transactions rt
where
rt.publisher_database_id = @publisher_database_id and
not xact_id = 0x0
order by xact_seqno desc
end
-- If (1) requesting truncate lsn (distbackuplsn), (2) sync with backup is set
-- query the values from MSrepl_backup_lsn
else if @sync_with_backup = 1
begin
-- Get the last backed up lsn if available.
select top 1 @max_xact_id = valid_xact_id, @max_xact_seqno = valid_xact_seqno
from
MSrepl_backup_lsns
where
publisher_database_id = @publisher_database_id
end
-- If @publisher is not null, we are calling this sp from sp_replrestart
-- Don't return result set.
if @publisher is null
select @max_xact_id, @max_xact_seqno, @publisher_database_id
-- Don't return any result when requsting a truncate lsn and
-- the database is not in 'sync with backup' mode, which signal the
-- distribution agent to use last dist lsn to call sp_repldone.
where not (@sync_with_backup = 0 and @for_truncate = 1)
end
存储过程中，代码
else if @sync_with_backup = 1
begin
-- Get the last backed up lsn if available.
select top 1 @max_xact_id = valid_xact_id, @max_xact_seqno = valid_xact_seqno
from
MSrepl_backup_lsns
where
publisher_database_id = @publisher_database_id
end
可见，报错中的dist_backup_lsn取自表MSrepl_backup_lsns
我们来查询一下该表：
select * from MSrepl_backup_lsns
列valid_xact_id为最后备份的事务ID，valid_xact_seqno为该事务中的LSN，即valid_xact_seqno为报错中显示的dist_backup_lsn，（请勿与图中贴出的错误进行对比，上图仅为错误示例截图）。
我们再去分发库MSrepl_transactions表中找到当前已经被读取到分发库的最大事务的LSN
SELECT TOP 1 * from
[dbo].[MSrepl_transactions] as t
where t.publisher_database_id = 5
ORDER BY t.entry_time DESC
表中给出的最大事务号是:0x00000026000001B60004，而上面dist_backup_lsn为：0x0002030D00003E7A0004，果然不一致。
但是我们细想，MSrepl_backup_lsns表中的记录半小时才更新一次（跟随分发库备份频率），而MSrepl_transactions中的记录是实时变化的，他们两者在比较繁忙的OLTP系统中是不太可能一致的！！但是为什么在切换的时候会去检查一致不一致？而我如果不切换，只将Logreader重启，它是不会去检查的。
所以我想：这个微软也许最后给我的答案就是他们常用的“By Design”。
先不说这个，我们来继续找一下到底是哪个存储过程在对比这两者，先找到该错误信息所属的MessageID:
SELECT * FROM sys.messages WHERE text LIKE '%分发数据库中可能存在%'
然后我们去系统表中寻找使用了这个message_id的系统存储过程：
SELECT b.name,a.definition FROM sys.all_sql_modules a
INNER JOIN sys.all_objects b ON b.object_id = a.object_id
WHERE a.definition LIKE '%18846%'
Oh,shit! 线索中断了，我们找不到一个系统存储过程使用了该message_id，所以这个判断应该是在系统扩展存储过程中。
总结：说到现在，不知道各位看官是否看明白，当你在分发库开启了sync_with_backup选项，在进行镜像切换时，如果出现：
分发数据库中可能存在不一致的状态: dist_backup_lsn {00020191:000022ff:0004}，dist_last_lsn {00020191:00002435:0004}
。请执行"sp_repldone NULL, NULL, 0, 0, 1"，然后执行sp_replflush。请重新初始化对发布的所有订阅。
这样的错误，请不要慌张，执行分发库备份作业即可。
