Compare fails for partitioned tables

DBA_Dave

I have been trying to compare two different cases in SQL 2005 SP2:

1. A non-partitioned table to its partitioned twin on another server.*

2. A partitioned tabled to its partitioned twin on another server.

In each case the compare hangs at 0% and blocks access to the target table.

Is this a known problem?

* I realize that all 2005 tables are technically partitioned. These actually have a partition scheme, file groups, and a partition function.

richardjm

I've not come across this before at all. Presumably these are both live databases and not backups. We've tested partitioned tables internally.

Is there any way you can give us the schema information for the tables or how you have them setup so we can attempt to reproduce this internally?

Are you using a primary key or custom comparison key as this will have a siginificant difference in performance?

DBA_Dave

I am using a date column, not the PK. This is for financial data that needs to be organized by quarters and years.

I can email the schema, I can't make it public.

richardjm

I do wonder if this is the sql server locking up whilst trying to arrange the table in date order. If you don't have a unique constraint set up on the date and if it's a big table this could take a considerable time.

Might be worth setting up a constraint or just trying to select from the table in that order in SQL Query Analyzer to see if takes as long to return as this sounds like it could be the problem.

DBA_Dave

I don't have any problems inserting to or selecting from this partitioned table.

The table has both a clustered index and a covering index on the time column used in the partition function. Nothing needs to be arranged.