Last post from me on SQL Server replication was on How to validate subscribers in SQL Server Replication. This article shows how the same can be done using TSQL.
sp_publication_validation:
This system stored procedure does a validation for each and all articles in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_publication_validation [ @publication = ] ‘publication’
[ , [ @rowcount_only = ] type_of_check_requested ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @publisher = ] ‘publisher’ ]
@publication is the name of the publication.
@rowcount_only is whether to return only the row count for the table.
0 for perform a SQL Server 7.0 compatible check sum
1 for perform rowcount only
2 for perform a row count and binary check sum.
@full_or_fast is used to calculate the row count.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@publisher specifies a non-sql server publisher.
You need to have sysadmin or db_owner role in the publication database to execute stored procedure.
sp_article_validation:
This system stored procedure does a validation for each article individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_article_validation [ @publication = ] ‘publication’
[ , [ @article = ] ‘article’ ]
[ , [ @rowcount_only = ] type_of_check_requested ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @subscription_level = ] subscription_level ]
[ , [ @reserved = ] reserved ]
[ , [ @publisher = ] ‘publisher’ ]
@publication is the name of the publication in which the article exists.
@article is the name of the article to validate.
@rowcount_only specifies if only the rowcount for the table is returned.
0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.
1 for perform a rowcount check only.
2 for perform a rowcount and binary checksum.
@full_or_fast is the method used to calculate the rowcount.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@subscription_level specifies whether or not the validation is picked up by a set of subscribers. If 0, validation is applied to all Subscribers. If 1, validation is only applied to a subset of the Subscribers specified by calls to sp_marksubscriptionvalidation in the current open transaction.
@reserved identified for informational purposes only.
@publisher specifies a non-Microsoft SQL Server Publisher.
You need to have SELECT ALL permissions on the source table for the article being validated
sp_table_validation:
This system stored procedure does a validation for each table individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.
sp_table_validation [ @table = ] ‘table’
[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]
[ , [ @expected_checksum = ] expected_checksum OUTPUT]
[ , [ @rowcount_only = ] rowcount_only ]
[ , [ @owner = ] ‘owner’ ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @table_name = ] table_name ]
[ , [ @column_list = ] ‘column_list’ ]
@table is the name of the table to be validated.
@expected_rowcount specifies whether to return the expected number of rows in the table. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.
@expected_checksum specifies whether to return the expected checksum for the table. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.
@rowcount_only specifies what type of checksum or rowcount to perform.
0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.
1 for perform a rowcount check only.
2 for perform a rowcount and binary checksum.
@owner is the name of the owner of the table.
@full_or_fast is the method used to calculate the rowcount.
0 does full count using count(*)
1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.
2 does a count using rows column in sysindexes table if there is a difference then it does a full count.
@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.
@table_name is the table name of the view used for output messages.
@column_list is the list of columns that should be used in the checksum function.