Validating Replication Subscriber using TSQL

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.

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
Monitoring Replication using Scripts(Version 1.0)
This script can be used to monitor replication using scripts instead of logging into the server and using replication monitor. It gives informati...
Adding article to existing publication without generating a full snapshot
This SP can be used to add articles to the existing publication without starting snapshot for all the tables. When Snapshot is started it will c...
powered by RelatedPosts