Adding a BIT column - 100+ hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?ALTER TABLE ADD...NOT NULL DEFAULT... is a fully logged operation. The logs that contain internal page updates are small but numerous (and I use this word loosely). There is nothing you can do at this point because as you mentioned correctly, - the rollback will take even longer. And as I stated in one of my replies to you previous similar post, - if you kill the service the database recovery will take forever (weeks possibly). There is however a rather drastic approach that may (!!!) yield a quick return your database to pre-ALTER state...I would not recommend you trying it simply because it may or may not succeed, and if it doesn't, - someone is gonna be fired...|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......
I'd hope so...Did they?|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?

From a design perspective, the column should be non-nullable. I'm sure we could deal with it as a NULLable column if absolutely necessary. That's a sacrifice I'd easily be willing to make but at this point it's too late.

As I said, I didn't do this and it's frustrating from my perspective. We really didn't need the column on this table in the first place and it was a without question a bad move. If he started both column add operations at the same time before he realized what a drastic operation this would be then I can understand his perspective. Believe me, I am using the info I learn here, and trying to convey what I learn to my team.

Thanks you both for the feedback! I will not cancel, let it run, just cross my fingers that it finishes in the next week, and I will try to convince my boss to give me yet another database server so that we can start over (not fun) as a parallel strategy. Thankfully, this was done on a secondary system where we are trying to reconstruct our primary database with a slightly improved process (bug fixes and better data gathering).|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......

LOL! No, no backups.

For some reason I feel way more stressed about this than either my boss or the worker who is assisting on this project.|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?

"The other" way? :) Mysterious...

I just emailed our part time DBA but I'm not counting on much. Unfortunately, we are mostly programmers.|||Threads like this just make me speechless.

It's hard to talk when you're rolling on the floor laughing.sql