May 11, 2009 at 2:29 pm
Hello,
I have 3 separate SQL servers (2 SQL 2005 and 1 SQL2000) handling the one way transactional replication process. Publisher and Subscribers are running on separate SQL 2005 servers while the distributor is running on a SQL 2000 server.
I'm replicating all tables for this database on a daily basis and the status always indicates replication has been successfully applied. But the physical size of the drive is only about 1/4 of the publisher's database size. I'm concern that not all the data has been replicated over. Is this behavior normal? How do I check the integrity of the subscriber DB?
*Snapshot are taken and applied only if i reinitialize the subscription, so it doesn't happen too often
Thank you in advance,
Jason
May 11, 2009 at 10:55 pm
Hi Jason,
Size of the database need not match. Database has many other objects, not just tables.
Run sp_publication_validation to check.
Suresh
May 11, 2009 at 11:03 pm
Size of the databases can also include much unused and unallocated space. And the tables themselves can also can varying amounts of currently unused space. Liewise, Indexes can be quite different as well.
If you suspect that a table is not fully replicated, start by comparing the RecordCounts of the two tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 8:22 am
Thank you both for your replies. I randomly checked a few tables between the publisher and the subscriber, they seemed to match. I will run the sp to validation the publication, but this certainly puts my mind at ease 🙂
May 13, 2009 at 12:21 pm
jliu (5/12/2009)
Thank you both for your replies. I randomly checked a few tables between the publisher and the subscriber, they seemed to match. I will run the sp to validation the publication, but this certainly puts my mind at ease 🙂
Keep in mind that validation procedures are useful only at times where no changes are happening on the primary.
* Noel
May 13, 2009 at 1:27 pm
jliu (5/12/2009)
Thank you both for your replies. I randomly checked a few tables between the publisher and the subscriber, they seemed to match. I will run the sp to validation the publication, but this certainly puts my mind at ease 🙂
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply