Unfortunately even using an INSERT INTO SELECT with TF 610 into an empty table I wasn’t able to get it minimally logged. Next I looked at The Data Loading Performance Guide. My initial attempt to copy the data was using an SSIS package (import wizard). Drop all of the indexes, create my new table, copy data, rename and re-create the indexes. And that one fact was enough to give me my solution.Ī variation on the second suggestion. It turns out that ~400 GB of the space used by the table are indexes (no comments please, I didn’t design it). Then I started looking at the table a bit closer. Maybe extend my log to another drive temporarily and just let the log grow as large as it needs to then clean up afterwards. Well that leaves me right back where I started.
![sql server alter table add column sql server alter table add column](https://blog.sqlauthority.com/wp-content/uploads/2016/03/multicoladd3.jpg)
Here were my thoughts on the first suggestion, adding a new column. Create a new table, populate it, index it, drop the old table, re-name the new table.
![sql server alter table add column sql server alter table add column](https://www.codeproject.com/KB/database/modified_on/modified_on.png)
SQL SERVER ALTER TABLE ADD COLUMN FULL
After about an hour I got back a log full error. My first attempt was just a straight ALTER TABLE ALTER COLUMN. I can get more disk space by requesting a new disk but I want to avoid the paperwork and complications of moving some of the files to the new location.This is a test environment so I can play a bit, but I’m planning for the production change.
![sql server alter table add column sql server alter table add column](https://i.stack.imgur.com/Dr42N.png)
I only have to worry about the log size of the largest single transaction.
![sql server alter table add column sql server alter table add column](https://powerbidocs.com/wp-content/uploads/2019/12/ALTER-Table-Statement-SQL-SERVER.png)
SQL SERVER ALTER TABLE ADD COLUMN FREE
Feel free to give me your opinion in the comments below. I will not be giving any specific examples or code. I had a task, I went through several possibilities and I’m going to share each of them and the results of those I tried. Fair warning, this is a discussion piece.