February 12, 2007 at 11:57 am
For internal design reasons, we need to remove a columns IDENTITY definition. Our table holds 180 M rows. Running into a slight gliche here.
When I change the table def (in Ent. Mgr 2000) I encounter this:
SQL Server 2000 copies the existing 180 M rows to a TEMP table then renames the TEMP table to the original table name. This copy is taking a LONGGGG time as it is a LOGGED operation and we don't have the remaining space to accomodate the temp 180 M row table. (Our table is being replicated so we cannot set the RECOVERY MODE to SIMPLE for this DB)
Is there a TRICK to do this more efficiently?
Am I limited to running this statement and leaving Identity_Insert ON forever?:
set identity_insert my_table ON
February 12, 2007 at 1:49 pm
Your table is being replicated and you want to do this? That means every change has to be sent to the subscriber table and done there also.
You are better off:
1. dropping replication
2. putting the db into simple mode
3. making the change
4. putting the db back into full mode
5. recreating replication.
The problem you are seeing is the way columns are dropped. The columns you want are copied to a new table (SELECT INTO), the original table dropped, the new table renamed.
-SQLBill
February 12, 2007 at 2:19 pm
I have to ask. Why do you have to do this after loading that much data into the table?
February 12, 2007 at 5:42 pm
There is no trick that will make your problem go away, you simply can't remove the identity property from a table. You can do what Enterprise Manager is trying to do and build a new table without the identity property and copy all the data over. Plus indexes, foreign keys, extended properties, etc. etc.
You have to do everything SQLBill listed, plus full backups before changing anything and after restoring FULL recovery mode. If you are short on disk space and want a more efficient trick, you could dump the table with BCP OUT, truncate it, modify it, and reload it with BCP IN. If you use a disk other than the SQL Data drives this should be about as efficient as you can get.
February 13, 2007 at 8:41 am
As an alternative approach, I'd look at trying to export the table to a file, truncate the table, and bulk load (bcp) the exported file into the recreated table (without the identity). Look up "minimally logged bulk copy" in SQL Server Books Online for reference to see that your database mode is set so bulk copy won't log transactions. This will run faster and prevent storage space issues.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply