Insert Column In Huge Datbases.

[joejack0330]

We keep our sales detail history in separate databases by year and each year has about 25 million records and each database is about 8-12 gig in size and we have about 9 years to do. We have union queries that just select * from each table. This works well but we need to add a field to these tables and would like to insert it in the middle of the table. I tested on my test sql box with one year and it took about 45 minutes to do this but real problem is the database grew from 12 gig to over 28 gig and transaction log goes to 17+ gig. This is ok for some of the history because we can do one at a time and the one drive has plenty of free space then we can clean up log and shrink db back down to about original size. But a couple of the databases are on a drive which doesn't have enough free space to handle. I know we could temporarily move these to another drive and do what we need to do then move back but is there another way to do it? Can we turn off transaction logging or anything just while we are doing it? We'd have to do it when no one else is on system and make sure to have a good back beforehand. We probably have enough space for database growth but not the extra 17 gig of log file.

Thanks, Joe

 

[gmmastros]

we need to add a field to these tables and would like to insert it in the middle of the table.

Why is it important to insert the column in to the middle of the table? Column order should not matter at all. I mean.... when you display data to a user (in a report) or export data to a file, or anything else I can think of, you should be specifying the column names to use. When done this way, it doesn't matter what the column order is, so it would not matter that the new column is inserted at the "end" of the table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joejack0330]

Thanks, I know and believe me, I tried to explain this to my boss but he has a real obsession with keeping things in order. I try to explain it to him and even though it will probably mean a whole Saturday of work for me, he wants it this way!

 

[gmmastros]

We probably have enough space for database growth but not the extra 17 gig of log file.

It's usually not a good idea to put the data files on the same drive as the log files. So... how about you toss in another drive, put the log file on this new drive, and then add your new column?

When your data and log files are on separate drives, you will be better performance, and drives are cheap anyway. Ya know?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joejack0330]

Thanks, I actually think the log files for the 2 databases on drive with less space might be on another drive so might not be a problem because should have enough space for the the database growth if that is true. Our older history databases from previous years might have trans logs on same drive but these are read only and don't get updated.

Thanks again, Joe

 

[RiverGuy]

Why is it important to insert the column in to the middle of the table? Column order should not matter at all. I mean.... when you display data to a user (in a report) or export data to a file, or anything else I can think of, you should be specifying the column names to use. When done this way, it doesn't matter what the column order is, so it would not matter that the new column is inserted at the "end" of the table.

George, I agree with you in most cases. However, there are times when I do see the need for column order to be important. We worked on a large project last year wherein the specs changed very regularly. Because of multiple synchronization paths, we have a set of several metadata columns at the end of the table. To me, it just looked too goofy to have meaningful columns, a handful of row metadata columns followed by more meaningful columns. The reasoning was more administrative than reporting.

It was a painful process to alter the table, but well worth it to create a series of scripts to script out the table DDL, alter the DDL, back up the data into a temporary spot, and then refill the new table from the old table.

 

[bborissov]

RiverGuy,
From a programmer point of view, the order of the columns SHOULD NOT matter.
DBA's troubles is other point of view (Hey, that's what they are paid for, to have troubles)
And because a programmer very soon becomes DBA I agree with you

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jsteph]

joejack,
I have done this and in my opinion, it's quicker if you create a new table with the new column, and insert from the original into the new using a stored proc that loops on a date, inserting a day at a time.

This commits each set on the day and the log will only grow by the day's amount of space, and then--and I'm not fully sure of your actual situation so this may not make sense--you delete that day from the original table.

So each day loop in the cursor you're loading to the new and deleting from the old for that day only.
--Jim