DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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
       *****************************************************