Mirror Switch LogReader agent error: Inconsistent status may exist in the distribution database

Software Environment: Windows Server R2 SP1 SQL server R2 SP2

Schema: ServerA (body) +serverb (mirrored) +serversub (subscription) +serverdist (distributed)

Special Note: The Serverdist distribution server is a standalone machine, so the sync_with_backup option is turned on.

About Sync_with_backup:

Original intention: Simply, turn on this option so that the single-machine distribution library will be able to continue to work by restoring to a new server with the most recent system database and user database backup when it goes down.

It is important to note that this option is turned on in the distribution library, which means that the log for the publishing library is truncated only after the distribution library has been backed up.

Check to see if this option is turned on:

     SELECTfromWHEREto like' distribution% '

Turn on this option:

sp_replicationdboption ' distribution ',' sync with backup ',' true '

Scene:

In a production environment, the distribution library is backed up half an hour.

LogReader Agent error when switching the subject from ServerA to ServerB:

Intuitively, LogReader believes that DIST_BACKUP_LSN and DIST_LAST_SN are inconsistent, leading to their inability to continue working. Let's actually look at whether the two are consistent.

The following is a list of steps LogReader, Distribution Agent work, for convenience, I posted it:

Log Reader Agent (Logread.exe) –Sequence  ofSteps
1.Calls sp_msadd_logreader_history to Write  tomslogreader_history– "Starting Agent"
2.sp_mshelp_logreader_agentid–obtain Log Reader AgentspecificInformation forThat publication
3.sp_mshelp_profile–obtains profile Information forThe Log Reader
4.msadd_logreader_history to Writemslogreader_history– "Initializing"
5.sp_msget_last_transaction–determinewhereThe Log Reader Agent Left offReading the log.
6.ReadTheTransactionLog–sp_replcmds
7.Processes the returned commands fromThe SP_REPLCMDSinchBatches byCalling sp_MSadd_repl_commands
8.Marks thisTransaction  asCommittedinchDistributionDatabase  by usingSp_repldoneprocedure 
9.Adjusts theIdentityRangeifNecessary and ifYou is usingAutomaticIdentityRange Management y calling Sp_mspub_adjust_identity
10.Calls sp_msget_last_transaction to CheckThe Last Transaction Read  andStoredinchMsreplication_transactionsTable 
11. when  AllTransactions is Read, LogRead.exe calls Sp_msadd_logreader_history andWrites message toMslogreader_history "1 Transactions with9 commands were delivered "
distribution Agent (Distrib.exe)-Sequence  ofSteps
1.master.db.sp_msget_jobstate–GetThe status ofThe Job (ifIt isAlready started)
2.sp_msadd_distribution_history–msdistribution_history–starting Agent
3.sp_mssubscription_status–whether Subscription has expiredorThe snapshot isReady
4.sp_server_info-determines theCollation 
5.sp_mshelp_subscriber_info–retrieve Subscriber Information
6.sp_mshelp_subscription_agentid–determine the name ofThe Distribution Agent
7.sp_msadd_distribution_history–initializing message–msrepl_distribution_history
8.sp_msadd_distribution_history–connecting toSubscriber-msrepl_distribution_history
9.so_datatype_info– toDetermine theDataType mapping Necessary to CreateThe trackingTableNecessary forThe Distribution Agent
10.sp_mscheck_subscribe onSubscriptionDatabase–verifies thatSQLServer Agent Account is inchSysadmin anddb_ownerrole inchSubscriptionDatabase 
11.sp_mscreate_sub_tables onSubscriberinchSubscriptionDatabase–creates mssusbcription_agents andMSreplication_subscriptions tables
12.sp_msinit_subscription_agent–updates the Subscription agent information onSubscriptionDatabase 
13.Retrieves Transaction_timestamp andSubscription_guid toDetermine what Distribution Agent has already replicated toThe Subscriber. Transaction_timestamp correlates toXact_seqnocolumn inchMsreplication_transactionsTable inchDistributionDatabase. All Values Large thanThe xact_seqno'll be replicated
14.IfWe isDoing initial sync, distribution Agent calls Sp_msupdatelastsyncinfo which updates msreplication_susbcriptions andMssusbcription_agentsTable 
15.Starts toRetrieve AllTransactions andTheircorrespondingCommands fromMsreplication_transactions andMsreplication_commandsTable whereTransaction_timestampcolumn inchSubscriptionDatabase< xact_seqnocolumn inchMsreplication_transactionsTable. Applies theTransaction usingSp_ms_get_repl_commandsprocedure 
16.IssuesDynamic SQL  to UpdateThe MSreplication_subscriptionsTable  withThe LastDeliveredTransactionId
17.sp_msdistribution_history to WriteThe Msrepl_distribution_historyTable  withStatus message "NNTransactionS withnn command (s) were delivered "

In step 5, we learned that LogReader used the stored procedure sp_msget_last_transaction to locate the last transaction in the Publish library log that was written to the distribution repository, and we found the source of the stored procedure:

CREATE PROCEDURESys.sp_msget_last_transaction
(
@publisher_idint=NULL,
@publisher_db sysname,
@publisher sysname =NULL,
@max_xact_seqno varbinary (16) =NULL Output
, @for_truncatebit= 0
)
 as
begin
    Declare@publisher_database_idint
    Declare@max_xact_id varbinary (16)
    Declare@sync_bitint
    Declare@sync_with_backupbit

    SetNocount 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 fromMaster.dbo.sysserverswhere
            UPPER(srvname) =UPPER(@publisher)

    --Get publisher database ID.
    SELECT@publisher_database_id = ID fromMSpublisher_databaseswherepublisher_id = @publisher_id and
publisher_db = @publisher_db

    if exists(Select* fromMaster.dbo.sysdatabaseswhere
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 Top1 @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
              notxact_id = 0x0
             Order  byXact_seqnodesc
    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 Top1 @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 isn't null, we are calling the 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 the last dist LSN to call sp_repldone.
            where  not(@sync_with_backup = 0 and@for_truncate = 1)
End

In the stored procedure, the code

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

Visible, the error in the DIST_BACKUP_LSN taken from the table Msrepl_backup_lsns

Let's check the table:

Select  from Msrepl_backup_lsns

The valid_xact_id for the last backup is id,valid_xact_seqno for the LSN in the transaction, i.e. Valid_xact_seqno is the dist_backup_lsn shown in the error (do not compare it to the error posted in the figure. Only the error example).

Let's go to the distribution library. The LSN of the largest transaction that is currently read to the distribution library is found in the msrepl_transactions table

SELECT TOP  from
     as T
where t.publisher_database_id = 5
ORDER  by T.entry_time DESC

The maximum number of transactions given in the table is: 0x00000026000001b60004, and the above DIST_BACKUP_LSN is: 0x0002030d00003e7a0004, it is indeed inconsistent.

But we think that the records in the Msrepl_backup_lsns table are only updated once in half an hour (following the distribution library backup frequency), and the records in MSrepl_transactions are changed in real time, and they are unlikely to be the same in a busy OLTP system!! But why check for inconsistencies when switching? And if I do not switch, only logreader restart, it will not be checked.

So I think: This Microsoft may finally give me the answer is their common "by Design".

Let's just go ahead and find out what the stored procedure is comparing the two, first find the MessageID that the error message belongs to:

SELECT  from WHERE  like % ' may exist in '% distribution database '

Then we go to the system table and look for the system stored procedure that uses this message_id:

SELECT  from Sys.all_sql_modules  a
    INNER JOIN  on b.object_id = a.object_id
WHERE  like '%18846% '

oh,shit! The clue is broken, we can't find a system stored procedure uses the message_id, so this judgment should be in the system extended stored procedure.

Summary: Speaking of now, I do not know whether you crossing understand, when you open the Sync_with_backup option in the distribution library, when the mirror switch, if it appears:

There may be an inconsistent state in the distribution database: Dist_backup_lsn {00020191:000022ff:0004},dist_last_lsn {00020191:00002435:0004}

。 Please execute "sp_repldone null, NULL, 0, 0, 1" and then execute sp_replflush. Please reinitialize all subscriptions to the publication.

Such a mistake, please do not panic, perform the distribution library backup job can be.