(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 129-------------------------------------------------- Transaction Replication Scripts Troubleshooting By: srinivasma_exceldbp Date: December 30, 2014, 5:46 am --------------------------------------------------------- /*Listing 1: Checking for immediate_sync property and changing its setting.*/ --Find if publication has immediate_sync property enabled EXEC sp_helppublication @publication = 'your_publication_name' --Disabling immediate_sync property EXEC sp_changepublication @publication = 'your_publication_name', @property = 'immediate_sync', @value = 'false'; GO /*Listing 2: Querying MSrepl_errors for latest replication errors.*/ --This is my go to table for finding errors. SELECT TOP 300 * FROM distribution.dbo.MSrepl_errors (NOLOCK) WHERE time > GETDATE() - .05 ORDER BY time DESC; GO /*Listing 3: Using sp_browsereplcmds to view a specific batch of commands.*/ --Paste in the seqno into both variables in this command EXEC sp_browsereplcmds @xact_seqno_start = '0x0000059C00001EF3001C', @xact_seqno_end = '0x0000059C00001EF3001C'; GO /*Listing 4: Useful scripts to find large batches of commands.*/ --Locate large batches SELECT COUNT(*) , xact_seqno FROM MSrepl_commands (NOLOCK) --where xact_seqno > 0x0017B2C50003BDF40008000000000000 GROUP BY xact_seqno --ORDER BY xact_seqno HAVING COUNT(*) > 1000 ORDER BY COUNT(*) DESC --Total by batch and article SELECT cm.xact_seqno , ma.article , s.publisher_database_id , COUNT(*) AS CommandCount FROM msrepl_commands cm ( NOLOCK ) JOIN dbo.MSsubscriptions s ( NOLOCK ) ON cm.publisher_database_id = s.publisher_database_id AND cm.article_id = s.article_id JOIN MSpublisher_databases d ( NOLOCK ) ON d.id = s.publisher_database_id JOIN msarticles ma ON ma.article_id = s.article_id --and subscriber_db = 'analytics' --and d.publisher_db = 'AdventureWorksDW2012' GROUP BY cm.xact_seqno , ma.article , s.publisher_database_id ORDER BY xact_seqno --Total by table SELECT ma.article , s.article_id , COUNT(*) AS CommandCount FROM msrepl_commands cm ( NOLOCK ) JOIN dbo.MSsubscriptions s ( NOLOCK ) ON cm.publisher_database_id = s.publisher_database_id AND cm.article_id = s.article_id JOIN MSpublisher_databases d ( NOLOCK ) ON d.id = s.publisher_database_id JOIN msarticles ma ON ma.article_id = s.article_id --and subscriber_db = 'analytics' --and d.publisher_db = 'AdventureWorksDW2012' GROUP BY ma.article , s.article_id ORDER BY COUNT(*) DESC; GO /*Listing 5: Reinitializing all Subscribers for a publication.*/ EXEC sp_reinitsubscription @publication = N'Sales', @subscriber = N'all' ; GO *****************************************************