Customize Distribution Cleanup Batch Size
You can customize the Transaction and Commands delete batch size to delete in larger increments during distribution database cleanup.
Category: Inside Replication
Enabling Transactional Subscriber with Identity Ranges for Updates
Share this PostTransactional Replication published tables containing Identity Ranges are updated on subscriber via Replication Distribution Agent. This processes does not increment the subscriber’s table Identity Seed starting value. If you drop a Subscriber and want to bring it online as an updatable user database, first execute CHECKINDENT reseeding the table to highest value +
Read More »
Inside Distribution Cleanup Watermark
Share this PostThe Distribution database is a cache of undistributed commands that the Distribution SQL Agent jobs are responsible to deliver to their subscribers. This cache is populated by the Publisher LogReader Agent job as new data changes are detected. The primary tables holding the cache of undistributed data is MSrepl_transactions and MSrepl_commands. Share this
Read More »
Script to decrypt the @schema_option for a Transactional Replication Article
Share this PostThe code below helps decrypt @schema_option settings for Transaction Replication articles. Note the meaning of binary offset may change in future builds of SQL Server. Always check Microsoft docs for most accurate listing. You’ll find scheme_options listing documented at sp_addarticle. Code for this example now posted on https://github.com/ReplTalk/ReplScripts/blob/master/schema_option.sql Example: ---------------------------- --Set the schema_option
Read More »
ReplTip – Foreign Key Check Constraints
Share this PostI recently worked with a customer seeing very slow transfer rate with Distribution Agent. Looking at RPC:Completed events we saw each sp_Msdel_ took .5 second generating over 200,000 reads. What, 200,000 reads to delete 1 row? Query plan should so small reads with 1 delete like shown below. StmtText -------- Clustered Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID])
Read More »