Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2009-02-23 : 11:50:51
|
| Trying to add ID column to 150 million row, 6GB tbl & the DB's in simpled mode. Operation fails every time due to a full transaction log. I have now maxed out on disk-space and the log last grew to 65GB.Cannot figure out why adding a ID column on a 5GB table would grow a transaction log (in simple mode) 60GB and upwards? Back tested on desparet servers and get the same results. ALTER TABLE <name> ADD pk_id int IDENTITY(1,1) CONSTRAINT PK_PKID PRIMARY KEYAny ideas? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-23 : 13:16:59
|
| The problem is that it's trying to do an update to all rows, all at once. You might be better served to create an empty table the way you want it with the identity col defined and then import your data into that new table, drop the old one and rename the new one with the id column.Mike"oh, that monkey is going to pay" |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-23 : 13:25:33
|
| Yes, Thats one problem with altering,adding constraints,identity etc to large tables. Best way to go will be as posted above if you ask me. |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2009-02-24 : 02:55:49
|
| Thanks for this - work around implemented successfully.I'll look into why this operation grew a DB in simple mode to the orders of x12 upwards the size if the base table. |
|
|
|
|
|
|
Unable to add IDENTITY column due to T Log