Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: 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?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?.......
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Brett Kaiser
    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).

  7. #7
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by rdjabarov
    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.

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Threads like this just make me speechless.


    It's hard to talk when you're rolling on the floor laughing.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •