Using sp_publication_validation

Question

Using sp_publication_validation

Leeland

SSCrazy Eights

Points: 8959
More actions
August 5, 2010 at 10:58 am

#222132

I found the above system procedure available for 'verifying' your replication.
I have a SQL 2005 SP3 server that replicates to 7 different servers (sql 2000 and SQL 7.0 SP4)
I have 11 publications and roughly 50+ articles between them.
I have reviewed the BOL for the procedure but it doesn't make that much sense to me. I run a command like this
EXEC sp_publication_validation @publication = 'pb_database_1_to_server_database_2'
where the publication name is obviously 'pb_database_1_to_server_database_2' and the sql server is version 7
I get the following reply.
Generated expected rowcount value of 6756 for tb_purchasedink_xref.
So I verify each table on each server and sure enough the row counts match.
I then go on the subscriber and delete 5 random rows (don't worry I will put them back)
So now the rowcounts do not match SOURCE = 6756 and DEST = 6751
Re-run the above validation command and I get the same results
Generated expected rowcount value of 6756 for tb_purchasedink_xref.
So my question is...am I using this stored procedure wrong? If not then why am I getting the same message if I KNOW the row counts don't match...
Also I was under the impression that each time the procedure was run it adds rows to the msdb..sysreplicationalerts table with alert_error_code of 20574 (fail) and 20575 (success).
I am also not finding a row each time I execute the validation.
Any help is appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

When this Stored proc is run, it might be doing a count by checking on the sys.sysindexes. This sys.sysindexes does not update the count immediately as soon as the rows are deleted. There fore it could be giving a wring message.

-Roy

Leeland SSCrazy Eights Points: 8959 More actions · Answer 2

Hello Roy,

I had reviewed the BOL regarding that stored procedure and I was under the assumption that since I am running the procedure on a SQL 2005 server which replicates to a SQL 7 server I was limited to the following parameters

for the @full_or_fast parameter it doesn't not seem to make a difference if I choose 0 / 1 / 2

EXEC sp_publication_validation @publication = 'pb_database_1_to_server_2_database_2',

@rowcount_only = 1, -- For SQL Server version 7.0 Subscribers, only a rowcount validation is performed.

@full_or_fast = 2

Generated expected rowcount value of 6756 for tb_purchasedink_xref.

-- source table

SELECT COUNT(*) FROM tb_purchasedink_xref-- 6756

-- dest table on other server

SELECT COUNT(*) FROM rep_tb_purchasedink_xref-- 6751

I don't have a ton of experience with replication in sql 2005 (this is the first server I have set up and monitored).

I would have expected the system procedure to through an error after I removed the 5 rows and re-ran the validation but it doesn't appear to be doing that

pradeepkumar1418 SSC Rookie Points: 48 More actions · Answer 3

Hi

When i am trying to know the checksum value by using below command i am getting some value.

EXEC sp_publication_validation @publication = 'PUB',

@rowcount_only = 2

Aftre that i executed below command to cross check but it was misamatched checksum value

Select checksum(*) from Mytable

Please help me how we can cross check the checksum value?

-pradeep