“Repl Talk“

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.

 

 

image

 

 

--In Properties window select Steps, Run Agent, then Edit.

image

 

 

--On the Command: remove –Continuous located at the end of the command line.

 

image

 

 

--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 

  update [SalesLT].[Customer]

  set FirstName = 'Tran2'

  where CustomerID = 1

 

--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;0x01050000000
00000180:000000ed:0002  LOP_DELETE_ROWS                0000:0001d5c2  REPLICATE
00000180:000000ed:0003  LOP_INSERT_ROWS                0000:0001d5c2  REPLICATE
00000180:000000ed:0004  LOP_COMMIT_XACT                0000:0001d5c2  REPLICATE

 

--Mark all transactions as having been replicated.

  EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

 

 

--Re-run sp_replshowcmds, no pending transactions.

  sp_replshowcmds

 

--DBCC OPENTRAN 

  DBCC OPENTRAN

No active open transactions.

--Flush Transaction Log article cache

  sp_replflush

 

--3rd update transaction

  update [SalesLT].[Customer]

  set FirstName = 'Tran3'

  where CustomerID = 1

 

 

--Start LogReader

--Start Distributor

--Verify 'Tran3' was replicated to subscriber   

  SELECT top 1 CustomerID, FirstName

  FROM AdventureWorks_Peer_A.[SalesLT].[Customer]

 

  SELECT top 1 CustomerID, FirstName

  FROM AdventureWorks_Peer_B.[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.

 

Chris Skorlinski

Microsoft SQL Server Escalation Services

“Repl Talk“