Here’s a summary of the troubleshooting and a few suggestions:
-There was a concern that the replication agent(distribution) was not delivering commands
-We expected a Profiler trace on the subscriber server and found distribution was delivering about 5000 commands every 10 seconds
-The command(s) were calls to the sp_delete procdedure located on the C O R E D e l e t e d O b j e c t s table
-We executed the following command on the subscriber to find the last fully “delivered” command:
Select * from msreplication_subscrptions
-Using the value found, we checked the currently “being delivered” transaction:
select top 1 * from msrepl_transactions
where xact_seqno > 0x00058A9000052ED40127000000000000
order by xact_seqno asc
--0x00058AA50000ABEA00CD --Oldest undelivered transaction
-We then checked how many commands were in the transactions:
select count(*) from MSrepl_commands nolock
where xact_seqno = 0x00058AA50000ABEA00CD
--160,727,115 commands
-With approximately 5000 commands delivered every 10 seconds, we estimated close to 90~hours for the distribution agent to deliver all the commands
-We suspect that on the publisher, a large delete against the table “CoreDeletedObjects” occurred via the application E.g.:
DELETE FROM COREDELTEDOBJECTS WHERE VALUES = X
-By default, when a statement is executed in a transaction, like the DELETE, it is broken apart by the Log Reader agent into a separate command for each row deleted.
-For example
*Pseudo code*
INSERT INTO TABLE X VALUES(1)
INSERT INTO TABLE X VALUES(2)
INSERT INTO TABLE X VALUES(3)
INSERT INTO TABLE X VALUES(4)
INSERT INTO TABLE X VALUES(5)
BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 6
COMMIT
-The delete above would be converted into:
BEGIN TRAN
EXEC SP_MSDEL_X (1)
EXEC SP_MSDEL_X (2)
EXEC SP_MSDEL_X (3)
EXEC SP_MSDEL_X (4)
EXEC SP_MSDEL_X (5)
COMMIT TRAN
-Whereas the single delete would be very fast, the broken apart version would take close to 5x longer.
-There are multiple options for working around this problem:
*Break deletes/updates into smaller transactional batches. E.g.
BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 500
COMMIT
BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 500 AND COL < 1000
COMMIT
BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 1000 AND COL < 1500
COMMIT
*Issue large updates/deletes via a procedure call. For example
CREATE PROCEDURE SP_MONTHLYCLEANUP(DATE INPUT)
AS
DELETE FROM COREDELTEDOBJECTS WHERE VALUES = @INPUT)
EXEC SP_MONTHLYCLEANUP(5/21/2015)
-Then, replicate the procedure call:
Publishing Stored Procedure Execution in Transactional Replication
https://msdn.microsoft.com/en-us/library/ms152754.aspx
-On the subscriber, you would see:
EXEC SP_MONTHLYCLEANUP(5/21/2015)
Best regards,
-There was a concern that the replication agent(distribution) was not delivering commands
-We expected a Profiler trace on the subscriber server and found distribution was delivering about 5000 commands every 10 seconds
-The command(s) were calls to the sp_delete procdedure located on the C O R E D e l e t e d O b j e c t s table
-We executed the following command on the subscriber to find the last fully “delivered” command:
Select * from msreplication_subscrptions
-Using the value found, we checked the currently “being delivered” transaction:
select top 1 * from msrepl_transactions
where xact_seqno > 0x00058A9000052ED40127000000000000
order by xact_seqno asc
--0x00058AA50000ABEA00CD --Oldest undelivered transaction
-We then checked how many commands were in the transactions:
select count(*) from MSrepl_commands nolock
where xact_seqno = 0x00058AA50000ABEA00CD
--160,727,115 commands
-With approximately 5000 commands delivered every 10 seconds, we estimated close to 90~hours for the distribution agent to deliver all the commands
-We suspect that on the publisher, a large delete against the table “CoreDeletedObjects” occurred via the application E.g.:
DELETE FROM COREDELTEDOBJECTS WHERE VALUES = X
-By default, when a statement is executed in a transaction, like the DELETE, it is broken apart by the Log Reader agent into a separate command for each row deleted.
-For example
*Pseudo code*
INSERT INTO TABLE X VALUES(1)
INSERT INTO TABLE X VALUES(2)
INSERT INTO TABLE X VALUES(3)
INSERT INTO TABLE X VALUES(4)
INSERT INTO TABLE X VALUES(5)
BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 6
COMMIT
-The delete above would be converted into:
BEGIN TRAN
EXEC SP_MSDEL_X (1)
EXEC SP_MSDEL_X (2)
EXEC SP_MSDEL_X (3)
EXEC SP_MSDEL_X (4)
EXEC SP_MSDEL_X (5)
COMMIT TRAN
-Whereas the single delete would be very fast, the broken apart version would take close to 5x longer.
-There are multiple options for working around this problem:
*Break deletes/updates into smaller transactional batches. E.g.
BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 500
COMMIT
BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 500 AND COL < 1000
COMMIT
BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 1000 AND COL < 1500
COMMIT
*Issue large updates/deletes via a procedure call. For example
CREATE PROCEDURE SP_MONTHLYCLEANUP(DATE INPUT)
AS
DELETE FROM COREDELTEDOBJECTS WHERE VALUES = @INPUT)
EXEC SP_MONTHLYCLEANUP(5/21/2015)
-Then, replicate the procedure call:
Publishing Stored Procedure Execution in Transactional Replication
https://msdn.microsoft.com/en-us/library/ms152754.aspx
-On the subscriber, you would see:
EXEC SP_MONTHLYCLEANUP(5/21/2015)
Best regards,
