I am trying to use sp_publication_validation stored procedure to validate that my replication setup is working correctly. This procedure is pretty straightforward and is described here.

I am running this command:

EXEC sp_publication_validation @publication = 'MyPublication'
    ,@rowcount_only = 1     -- (1 - rowcnt only, 2 - rowcnt and checksum)
    ,@full_or_fast = 0      -- (0 - full count using COUNT(*), 1 - use sysindexes.rows, 2 - conditional)

Initially everything seems to be ok and the results indicate that all of the tables match. Next I go over to the subscriber and manually delete a few rows from some of the tables. I manually verify that the row counts in my tables are now different between the publisher and the subscriber. Finally I run the sp_publication_validation procedure again and .... it says that everything is still OK. This is wrong! I also tried to return both rowcnt and checksum and it still doesn't detect the fact that there are differences between the publisher and subscriber.

I appreciate any ideas.

Thank you!

  • What's your version of SQL server?
    – Stoleg
    Aug 23, 2014 at 0:04
  • It is SQL Server 2012
    – SQL_Guy
    Aug 24, 2014 at 17:49
  • Check this out: sqlmag.com/sql-server/validating-replicated-data
    – Stoleg
    Aug 24, 2014 at 19:39
  • Thank you, Stoleg! This is very helpful article and explains how these stored procedures are being used. Basically now I understand that they are meant to work in conjunction with the rest of replication infrastructure and not by themselves. Microsoft could certainly do a better job explaining this fact in the documentation!
    – SQL_Guy
    Aug 27, 2014 at 14:32

2 Answers 2


My explanation is that sp_publication_validation validates a Publication: set of Articles sent by Publisher and received by Subscriber. Since there were no data corruption in transit this SP does not report any errors.

To detect differences in replicated tables use SP_TABLE_VALIDATION.

Either returns rowcount or checksum information on a table or indexed view, or compares the provided rowcount or checksum information with the specified table or indexed view. This stored procedure is executed at the Publisher on the publication database and at the Subscriber on the subscription database.


  • Thank you for your suggestion! So I checked and it seems that sp_table_validation works the same way as sp_publication_validation, except at the individual table level. It does not compare the tables between the publisher and subscriber. For example, if I run it on publisher I get, say, 1000 records for "TableX". I then run it on subscriber and get 999 records fro "TableX". I guess it's up to me to capture the output and compare the two. If this is the case, I really don't see the value these stored procedures provide. I might as well write my own script.
    – SQL_Guy
    Aug 24, 2014 at 0:36

OK, here is how I ended up addressing this. I basically wrote a script to compare row counts between tables on publisher and the distributor. One per-requisite is to have a linked server between the two servers. In my case it is called "distributor_ls" (since distributor is on the same server as the subscriber). First part of the script gather information about published databases into a temporary table called #tmp_replcationInfo. The credit for this first part of the script belongs here

Second part of the script then uses this information to gather the table row counts into a temporary table called #result for those tables where row counts differ. The differences are then displayed. I know it may not be not super-reliable because it relies on row counts stored in sys.partitions table, but it does what I need.

I hope somebody finds it useful.

    -- 1. Gather publication information

    DECLARE @Detail CHAR(1) 
    SET @Detail = 'N' 

    IF OBJECT_ID('tempdb..#tmp_replcationInfo') IS NOT NULL DROP TABLE                 #tmp_replcationInfo
    CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 

    EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
    insert into #tmp_replcationInfo 
          db_name() PublisherDB 
        , sp.name as PublisherName 
        , sa.name as TableName 
        , UPPER(srv.srvname) as SubscriberServerName 
    from dbo.syspublications sp  
    join dbo.sysarticles sa on sp.pubid = sa.pubid 
    join dbo.syssubscriptions s on sa.artid = s.artid 
    join master.dbo.sysservers srv on s.srvid = srv.srvid 
    IF @Detail = 'Y' 
       SELECT * FROM #tmp_replcationInfo 

    FROM #tmp_replcationInfo 

    -- 2. Determine row count descrepancies

    IF OBJECT_ID('tempdb..#result') IS NOT NULL DROP TABLE #result
    CREATE TABLE #result (DBName SYSNAME, TableName VARCHAR(128), PublisherRowCnt BIGINT, SubscriberRowCnt BIGINT)

            @nSQL NVARCHAR(4000)

    FROM #tmp_replcationInfo 

    OPEN db_cur


SET @nSQL = 'INSERT #result (DBName, TableName, PublisherRowCnt, SubscriberRowCnt)
SELECT ''' + @DBName + ''', ISNULL(p.TableName, s.TableName), p.RowCnt, s.RowCnt
            t.name AS TableName,
            SUM(p.Rows) AS RowCnt
    FROM    ' + @DBName + '.sys.tables t
    JOIN    ' + @DBName + '.sys.partitions p ON t.object_id = p.object_id
    WHERE   t.is_ms_shipped = 0
    AND     p.index_id < 2 -- 0:Heap, 1:Clustered
    AND     t.is_published = 1
    GROUP BY t.name
) p
            t.name AS TableName,
            SUM(p.Rows) AS RowCnt
    FROM    distributor_ls.' + @DBName + '.sys.tables t
    JOIN    distributor_ls.' + @DBName + '.sys.partitions p ON t.object_id = p.object_id
    WHERE   t.is_ms_shipped = 0
    AND     p.index_id < 2 -- 0:Heap, 1:Clustered
    -- add more filters as needed
    AND     t.name NOT LIKE ''%Delta''
    AND     t.name NOT LIKE ''%Global''
    AND     t.name NOT LIKE ''RowVer%''
    GROUP BY t.name
) s
ON  p.TableName = s.TableName
WHERE ISNULL(p.RowCnt, 0) <> ISNULL(s.RowCnt, 0)

EXEC sp_executesql @nSQL



    CLOSE db_cur
    DEALLOCATE db_cur

    -- 3. Return results

    SELECT *
    FROM #result
    ORDER BY DBName, TableName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.