Stop Automated Validation

Question

We have transactional replication set up between two servers and everything seems to be working.

This is a pull subscription that is run every day shortly after midnight. In doing some performance benchmarking using SQL Profiler, I found the the system procedure sp_table_validation is being run for all tables involved in replication on the subscriber immediately following distribution. Checking over several days shows this validation is occuring with every distribution. No errors are being reported but the validation is pretty resource intensive and I would like to stop it.

I understand the validation for transactional replication is a one-time deal to be run during the next distribution. I can't find anything on the publisher or the subscriber that is initiating this. Profiler traces show that it is happening within the same SPID as distribution so I am presuming that it must be related to either the distribution agent or the distribution database. My best guess as to the cause is that validation was initiated at some point and, for whatever reason, some flag is stuck somewhere so validation is occuring with every distribution.

Any thoughts on how to find what is causing validation to continue and, more importantly, how to stop it would be greatly appreciated.

David Anderson

Answer 1

David,
It is recommended that you perform data validation for transactional replication periodically. You can refer to the section "Validate data periodically" in this article:
http://msdn.microsoft.com/en-us/library/ms151818.aspx

In SQL Server 2005/2008, there is no direct interface for you to disable the validation. However you can try making some changes on the validation settings so as to improve performance. By default, there are three options for row count validation:
1. Compute a fast row count based on cached table definition
2. Compute an actual row count by querying the table directly
3. Compute a fast row count; if differences are found, compute an actual row count.
The option 3 is the default. Also the validation has a default checksum option "Compare checksums to verify row data (this process takes a long time)".

If you want to optimize the performance, you may try choosing the option 1 on row count validation and uncheck the checksum option "Compare checksums to verify row data (this process takes a long time)".

You can do this at the publisher. Right click the publication under Replication->Local Publications in SQL Server Management Studio, click Validate Subscriptions..., check "Validate the following subscriptions", choose the subscriptions  for which you want to change the settings, click "Validate Options..." and you will find those options.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 2

Thanks for the quick reply

I forgot to mention that this is SQL 2000 - whether that makes a difference.

We have several other replications running with none of them being validated every time they run. On this particular subscription, the validation is occuring every time distribution is run. At present, distribution only takes a couple of minutes but the validation takes 15 mins or more to complete so you can see why we don't want it running all the time.

Are you telling that validation is set for this subscription and there is no way to disable?

Would dropping the subscription and recreating it resolve this issue?

If we can't stop it, is there any way to modify the scheduling so that is doesnt occur with every distribution.