Customize Distribution Cleanup Batch Size
You can customize the Transaction and Commands delete batch size to delete in larger increments during distribution database cleanup.
Author: Chris Skorlinski [MSFT]
Welcome to Camp GeekaLot for SQL DBAs
Back in 2010 I hosted a SQL DBA best practices discussion during the Charlotte SQL Server Users Group meeting. I’ve attached a couple of slides used during the discussion. If you ignore the “Will you upgrade to 64bit?”, its interesting to see much of the content still applies today. Welcome-to-Camp-GeekaLot-for-SQLDBA Enjoy.
Enabling Transactional Subscriber with Identity Ranges for Updates
Transactional 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 + 1. DBCC
Read More »
Peer 2 Peer Replication fails with Incorrect syntax near ‘sp_MS…’ (Source: MSSQLServer, Error number: 102)
Symptom Creating a Peer 2 Peer publication from SQL scripts and you observe the Distribution Agents failing with “incorrect syntax near”. These messages can be observed in SQL Server Agent job history, in SQL Profiler Trace, or in Agent -Output logging. Errors show below were raised on (insert) stored procedure for Production.Product table for AdventureWorks2017
Read More »
Slowdown Distribution History Cleanup for Troubleshooting
By default Replication is configured to purge historical data older then 48 hours. For troubleshooting, its nice to pause or postpone purging of replication agent historical data. To change the History Cleanup settings use SQL Server Management Studio, alt-click “Replication” folder and select “Distributor Properties”.