Skip Individual Log Reader Transaction

When the log reader agent hits a transaction it cannot replicate, it halts - and with it, the entire replication topology. This runbook walks through the full recovery procedure. First, reconfigure the log reader job to scan in batches of one (-ReadBatchSize 1) so you can pinpoint the exact bad transaction; note its xdesid and xact_segno from sp_repltrans on the publisher. Then mark that LSN as replicated with sp_repldone (log reader must be stopped), release log access with sp_replflush, and finally sync the distributor's MSrepl_transactions table to the same sequence number - backing up the table first, deleting the old row, inserting the new one, and verifying with sp_MSget_last_transaction. The last step is restoring the agent's default settings and confirming it runs clean.

/*
	In the very unlikely event that the SQL Log Reader Agent encounters an error, it will throw a rather
		friendly error and halt scanning records – which throws a big wrench in the replication topology.
		Fortunately, it is possible to skip the erroneous transaction and continue with the next. 
*/

--Finding The Actual Erroneous LSN--

---Modify the Log Reader SQL Agent Job---
/*
	You should first understand that the log-reader agent "scans the transaction log" to discover transactions that need to be replicated.
	It is configured by default (for efficiencies sake) to scan in batches of 500. The erroneous transaction could be any one of these 500,
	so you will need to reconfigure the agent to scan in batches of 1. This way the agent will succeed until it reaches the one transaction
	that is causing the error and you can therefor identify, isolate and subsequently skip it. 

	Ensure that the log-reader agent is running with a read batch size of 1. 
		Example parameters: -HistoryVerboseLevel 1 -PollingInterval 1 -ReadBatchSize 1 
	Let log reader run until the transaction that you want to skip has been encountered. 
		The agent will throw an error when it reaches the erroneous transaction. 
	Stop the log reader agent. 
*/

---Take note of the erroneous record---
/*
Run {{sp_repltrans}} on the publisher and get the {{xdesid}} and {{xact_segno}} from the returned results:
*/

exec sp_repltrans

--Update the Publisher--

---Mark the LSN as Replicated---
/*
	Now you will need to tell the publisher that the erroneous LSN has been pushed to the distributor by issuing the
		{{sp_repldone}} command. It is very important that the Log Reader SQL Agent Job is NOT running when this is done.

	Use the {{xdesid}} and {{xact_segno}} that you retrieved from {{sp_repltrans}} to run {{sp_repldone}} on the publisher:
*/

exec sp_repldone @xdesid = {xact_id}, @xact_segno = {xact_seqno}, @reset = 1

---Flush the Replication Cache---
/*
	You must run {{sp_replflush}} after you act in behalf of the log-reader agent

	Why? Well, only one Log Reader can access the log at a time and if a you have log reader access to a database,
		executing sp_replflush releases your access. Other clients (such as the SQL Agent Log Reader) can then scan
		the transaction log using sp_replcmds or sp_replshowcmds.
*/

exec sp_replflush 

--Update the Distributor--
/*
	The distributor keeps track of the replicated LSNs just the same as the publisher does - so we need to sync them both up
		because the SQL Agent Log Reader will try to match them up when its started back up. If they don't match, you'll receive
		an error to the effect:

	The specified LSN {xxxxxxxx:xxxxxxxx:xxxx} for repldone log scan occurs before the current start of replication in the log {yyyyyyyy:yyyyyyyy:yyyy}.
*/

---Get Supporting Information---
----Get the Publisher ID----

SELECT
	publisher_id,
	publication,
	publisher_db
FROM
	MSpublications
WHERE
	publication = '{Publication_Name}'
	AND publisher_db = '{Publisher_Database_Name}'

----Get the Published Database Id----
<sql>
SELECT
	publisher_db,
	id as publisher_database_id
FROM
	MSpublisher_databases
WHERE
	publisher_id = {publisher_id}
	AND publisher_db = '{Publisher_Database_Name}'
</sql>

---Replace the Last Replicated Sequence---
----Remove the Old "Last Replicated Sequence"----


--First make a backup:
SELECT * INTO MSrepl_transactions_Backup_ FROM MSrepl_transactions

--Then delete the old value from {{MSrepl_transactions}}:
DELETE FROM MSrepl_transactions WHERE publisher_database_id = {publisher_database_id}

----Insert a New "Last Replicated Sequence"----
/*
	You will need to insert the values from the first row returned by sp_repltrans at the publisher into the
		{{MSrepl_transactions}} table on the publications distribution database at the distributor:
*/

INSERT INTO MSrepl_transactions(publisher_database_id, xact_id, xact_seqno, entry_time)
SELECT {publisher_database_id}, {xact_id}, {xact_seqno}, getdate()

---Confirm the "Last Replicated Sequence"---
/*
	While this is not required, you can call {{sp_MSget_last_transaction}} to verify that the information you
		inserted into {{MSrepl_transactions}} is going to be seen correctly by the SQL Server Log Reader Agent.
*/

EXEC sp_MSget_last_transaction @publisher_id = {Publisher_Id}, @publisher_db = '{Publisher_Database_Name}', @for_truncate = 0

--Pulling the Trigger--
/*
	Set the log-reader agent settings back to their defaults. 
	Start the log reader agent and ensure that it is executing properly. 
*/