by Chris Skorlinski
Microsoft SQL Server Escalation Services
You can also view the video demonstration on YouTube.
I thought it might be helpful if I posted an example using using sp_repldone to mark all pending transactions as “replicated”. We’ve used this command to “skip over” a batch DELETE command that was accidently run on the Publisher. This prevented the DELETE from being pushed to the Distribution database and then down to the Subscriber. Now we could have allowed the Log Reader to pick up the rows then had the Distribution Agent skip, but the DELETE was 100+million rows. We found it easier to skip them at the Log Reader.
To recover the data we can Restore the Publisher or reload the DELETED records from the Subscriber table. For a 10TB database, reloading records from 1 table was much faster then a complete restored.
This command is found in SQL Server Books Online (all versions). The BOL states the publication should be initialized. This is a recommended to ensure the Publisher and Subscriber have the same data. However as we’ll see, this is not always required.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
--Create a Transactional Replication publications
--For demonstration purposes, edit the LogReader and Distribution
-- SQL Agent Job properties and remove –Continuous.
--Using Replication Monitor, right click the Log Reader Agent Job, then Properties.
--In Properties window select Steps, Run Agent, then Edit.
--On the Command: remove –Continuous located at the end of the command line.
--1st update transaction
update [SalesLT].[Customer]
set FirstName = 'Tran1'
where CustomerID = 1
--Start LogReader for 1 transactions (-continuous option was removed)
--Start Distributor
--Verify 'Tran1' was replicated to subscriber
SELECT top 1 CustomerID, FirstName
FROM AdventureWorks_Peer_B.[SalesLT].[Customer]
--2nd update transaction
set FirstName = 'Tran2'
--Show transactions pending in the Transaction Log
sp_replshowcmds
xact_seqno = 0x00000180000000ED0004
{CALL [dbo].[sp_MSupd_SalesLTCustomer] (,,,N'Tran2',,,,,,,,,,,,1,0x0800)}
--The DBCC OPENTRAN command be used to show if there are pending transactions.
--Notice the “Oldest non-distributed LSN” is the BEGIN Tran LSN for the same UPDATE
DBCC OPENTRAN
Replicated Transaction Information:
Oldest distributed LSN : (384:235:4)
Oldest non-distributed LSN : (384:237:1) >>>>>0x00000180 000000ED 0001
--The FN_DBLOG function can display the log contents from the
-- Begin Tran (DBCC OPENTRAN) 00000180:000000ed:0001
-- to the
-- Commit Tran(sp_replshowcmds) 00000180:000000ed:0004
select [Current LSN],[Operation],[Transaction ID], Left([Description],20)
from::fn_dblog('0x00000180:000000ED:0001','0x00000180:000000ED:0004')
Current LSN Operation Transaction ID ----------------------- ------------------------------ -------------- --------------------00000180:000000ed:0001 LOP_BEGIN_XACT 0000:0001d5c2 UPDATE;0x0105000000000000180:000000ed:0002 LOP_DELETE_ROWS 0000:0001d5c2 REPLICATE00000180:000000ed:0003 LOP_INSERT_ROWS 0000:0001d5c2 REPLICATE00000180:000000ed:0004 LOP_COMMIT_XACT 0000:0001d5c2 REPLICATE
--Mark all transactions as having been replicated.
--Re-run sp_replshowcmds, no pending transactions.
--DBCC OPENTRAN
No active open transactions.
--Flush Transaction Log article cache
sp_replflush
--3rd update transaction
set FirstName = 'Tran3'
--Start LogReader
--Verify 'Tran3' was replicated to subscriber
FROM AdventureWorks_Peer_A.[SalesLT].[Customer]
CustomerID FirstName
----------- ------------------------------
1 Tran3
Notice Tran3 was correctly replicated to the subscriber. So what’s the problem? Well, if there had been another transaction pending that I didn’t know about, it too would have been marked as Replicated and not picked up by the Log Reader. This would result in Publisher and Subscriber being out-of-sync. To correct, you had use TableDiff.exe utility included with SQL Server to find row/column differences, or initialize the subscriber
Hands on demos are a great way to learn Replication before problems arise. Try for yourself this quick demo. Then see if you can insert 2 transactions, but just mark the 1st as replicated keeping the 2nd transaction active for the Log Reader to pickup.
“Repl Talk“