January 9, 2007 at 7:31 am
We have SQL 2000, SP4
Our database consists of two tables and the size one of them is aprox 43GB. I need to add a new column to this table. When I tried to do that, the transaction log grew from 100MB to 7GB and the proccess stopped, because there were no more free space on the physical disk.
I know that every time when DBA tries to add a new column or tries to alter a column, the SQL server renames that table, creates the new one with the right name, and then copies data from the old table to the new one.
My question: is there any way to prevent SQL server to copy that data from one table to another during the column adding proccess or do I need to extend that disk?
Thanks
January 9, 2007 at 7:41 am
ALTER TABLE dbo.TableName
ADD ColName INT NOT NULL /*optional from here*/ CONSTRAINT DF_TableName_Init DEFAULT (0)
January 9, 2007 at 7:51 am
Thanks, I knew that there must be a way out. If I correctly understood, the key parts of this command are:
NOT NULL
CONSTRAINT DF_TableName_Init DEFAULT (0)
January 9, 2007 at 8:16 am
NOT NULLS means that the column is not nullable. If there already is data in the table and you need to add a non nullable column, then you have to set a default value.
If the column is nullable then it becomes simpler :
ALTER TABLE dbo.TableName
ADD Colname [Datatype]
GO
I can't tell you how much log space it will require to do this operation, but I would assume a lot less than 7 gig (fingers crossed).
January 9, 2007 at 2:04 pm
Yes, you were right. Transaction log remained the same size after the 'alter' statement:
ALTER TABLE dbo.TableName
ADD ColName INT CONSTRAINT DF_TableName_Init DEFAULT (0)
It's very strange, because one of our programmers was trying to do that(only without adding constraint) through Enterprise Manager and he made Transaction log file about 7GB. Maybe he tried to insert a new column between already existing ones instead of adding it to the end of the columns list...
Anyway Thank you!
January 9, 2007 at 2:12 pm
I don't know why it used the old copy / destroy / rename but that sure was the cause of your headaches.
January 9, 2007 at 3:10 pm
It may be that Enterprise Manager will do the copy/drop/rename method, but I think it will try not to, based on what you are doing. If you do insert a column row (not add the column at the end of the column list), EM must do the copy/drop/rename. If you have a update/insert trigger, it must do copy/drop/rename. I think (but am not sure) if you add a text or image datatype, it will do a copy/drop/rename.
Also, I seem to remember if you have default value, it may also copy/drop/rename (but I'm not sure on this one, I just remember for some reason having to do the default in multiple steps; add the column allowing nulls, set values with update query, set default constraint, set not null constraint). But I could stand corrected on that one.
Hope this helps
Mark
January 9, 2007 at 3:28 pm
Did you change the recovery mode to SIMPLE, since you run out of disk space?
January 9, 2007 at 4:38 pm
Do your best to NEVER use Enterprise Manager to make schema changes. It tends to CREATE and copy from old to new, drop old and rename new. A lot of overhead.
-SQLBill
January 9, 2007 at 11:21 pm
SQL ORACLE>
No I didn't. We often do transaction log backups and I was worried that changing recovery model from Full to Simple could bring a lot of problem to me when I'll need to restore that database.
Anyway, the problem was solved and thanks for the help and explanations
Vidas
January 10, 2007 at 4:50 am
Well never use the EM to alter a table which is huge because the EM creates a new table,copies the entire data there and then deletes the old table. So it is always going to increase the transaction log and also more time. Instead always used the QA for altering the table.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply