Notes on Log Reader Parameters MaxCmdsInTran and ReadBatchSize

Notes on Log Reader Parameters MaxCmdsInTran and ReadBatchSize Jose Moreira B. Neto Microsoft SQL Server Escalation Services From SQL Server BOL we have: -ReadBatchSize Is the maximum number of transactions read out of the transaction log of the publishing database per processing cycle, with a default of 500. -MaxCmdsInTran Specifies the maximum number of statements…

1

How to show pending Merge Replication changes

How to show pending Merge Replication changes Jonathan Clark Microsoft SQL Server Escalation Services SQL Server sp_showpendingchanges is used to determine how many changes need to be moved to a subscriber or uploaded to a publisher.  You can use these steps to “roll your own” and pull similar data. To tackle this problem from the…

8

Initialization of Merge Replication subscriber fails with primary key violation

Initialization of Merge Replication subscriber fails with primary key violation Troy Moen Microsoft SQL Server Escalation Services We encountered an unusual timing problem while pushing a Snapshot down to a merge subscriber.  We don’t expect many to encounter this problem, after all we’ve only seen it once in 15 years.  We wanted to share this…

0

Distribution Agent is blocking users on Subscriber while replicating INSERT, UPDATE, and DELETE statements.

Distribution Agent is blocking users on Subscriber while replicating INSERT, UPDATE, and DELETE statements. Chris Skorlinski Microsoft SQL Server Escalation Services In the middle of a huge phased migration from SQL 2000 to SQL 2008 we noticed the SQL 2008 publisher\distributor was causing blocking when replicating data changes to the SQL 2000 subscriber. We discovered…

1

How to set HistoryVerboseLevel Profile for all Agent

How to set HistoryVerboseLevel Profile for all Agent Chris Skorlinski Microsoft SQL Server Escalation Services This is a follow up post to my article Using “Verbose history agent profile.” while troubleshooting Replication. Using SQL Server Management Studio you can set a default profile for all Replication Agents.  In this walk through I’ll change ALL Merge…

0

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Specify all articles when subscribing to a publication using concurrent snapshot processing. Chris Skorlinski Microsoft SQL Server Escalation Services I recently encountered this error when setting up Transactional Replication via SQL scripts. Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533 Specify all articles when subscribing to a publication using concurrent snapshot processing. Looking…

1

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Replicating Non-Clustered Indexes Improves Subscriber Query Performance Jared Poché, MCSE, MCDBA SQL Server Sr. Support Escalation Engineer Product Support Services (PSS) – Charlotte, NC One of the advantages of replication is that subscribing servers can be used for reporting thereby offloading RO query activity from the Publisher. Recently I worked on a performance case on…

2

Marking Identity Columns as “Not For Replication” in existing Publication

Marking Identity Columns as “Not For Replication” in existing Publication Chris Skorlinski Microsoft SQL Server Escalation Services We encounter a problem today with a Distribution Agent failing with “Row Not Found” when pushing an UPDATE to a Subscriber.  Reaching the problem we discovered the Subscriber was created using a Backup\Restore from the Publisher and had…

3