Checking Replication Row Counts

• GrahamO-735217

  SSC Enthusiast

  Points: 173

  More actions

  July 27, 2008 at 8:21 pm

  #96513

  I would like to be able to see the row counts of the source tables and the destination tables of the tables being published.

  Is there a way to do this?

  I have been trying it via T-SQL and not getting very far. My idea was to build a stored procedure and then have a job email results when the row counts are out by X rows.

  The reason this came up is that after replication breaks, due to systems staff requesting server reboots and the like, I would like to know how broken the replication is.

  Thanks

  Graham

  Graham Okely B App Sc
  Senior Database Administrator

• GrahamO-735217

  SSC Enthusiast

  Points: 173

  More actions

  July 27, 2008 at 10:12 pm

  #848950

  Replication Checking

  Here is a handy tip for checking up on replication row counts.

  http://www.databasejournal.com/img/ReplicationValidation.txt

  Alerts can be set up to watch for error codes but note that error code 20575 is actually a pass code and 20574 is a fail code.

  Here is some T-SQL to help:

  select distinct CASE alert_error_code

  WHEN 20575 THEN 'Passed row count'

  WHEN 20574 THEN 'Failed row count'

  END + ' ' + RTrim(subscriber) + ' ' + RTrim(publication)

  FROM msdb..sysreplicationalerts

  WHERE (alert_error_code = 20575 OR alert_error_code = 20574) AND time > CONVERT(Varchar(12),Getdate(),110)

  Graham Okely B App Sc
  Senior Database Administrator