SQL 2008 Replication - Quandry

[johndou]

We are in the process of upgrading our main database from SQL 2000 to SQL 2008. We will be going with all new hardware. In testing a backup strategy, I've setup 3 SQL 2008 servers (publisher, distributor and subscriber).

I took a backup of our SQL 2000 DB, restored it to the publisher server and published it out to the subscriber. The replication runs without errors. However, the DB file on the publisher is 37 Gigs (as well as on the SQL 2000 production box) and it's 18 Gigs on the subscriber.

I've checked table counts, row counts and compared data. It all seems to be there.

Any ideas why there would be such a difference in DB file size? The DB on the publisher and production 2000 box only shows 1.2 gigs of free space. Does SQL 2008 compress the data more efficiently or something?

 

[SQLBill]

Have you checked Indexes? If you hadn't rebuilt/defragged indexes on the original server they could be taking up a lot of space. Since the new publisher is from a backup of the original server, the indexes will still be fragmented. On the new subscriber the indexes haven't had a chance to get fragmented yet.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875