Post reply

Running low dispace on drive with database files

  • June 23, 2012 at 8:38 am

    #260388

    I am running low dispace on drive where I have all the db files. What do I do now? Please help.

  • June 23, 2012 at 9:00 am

    #1504749

    Consider adding more space or plan to move the database to a drive with more space.

    I would suggest you to check with some senior DBA in your team.

    M&M

  • June 23, 2012 at 10:16 am

    #1504752

    As stated, your best course of action is to get more disk space allocated to the server or to move some of the databases to a different volume that doesn't have the space issues.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 23, 2012 at 3:21 pm

    #1504775

    Which recovery mode are you in and are you taking at least nightly log backups if the recovery mode is "FULL"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 23, 2012 at 5:47 pm

    #1504785

    FULL. Yes nightly backup (every 24 hours)

  • June 23, 2012 at 6:08 pm

    #1504786

    What about the transaction log backups?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 23, 2012 at 6:35 pm

    #1504787

    Every 15 minus...And diff backup every 1 hour

  • June 23, 2012 at 7:59 pm

    #1504790

    Ok.... all that means so far is that we can't blame not backing up the log file as the cause. Still, you might want to check the logfile and see what size it is. Also check and see if TempDB has grown out of proportion because of any runaway queries.

    Rebuilding indexes may help if any splits occurred over time.

    Still, that's not going to buy you much. You'll probably have to bite the bullet and buy some more hard disk space like the others said.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 26, 2012 at 2:57 pm

    #1505856

    Are you manually growing your database files on a regular basis, or are your database files auto-growing regularly? What made you realize you were running low on disk space? When did you notice?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 26, 2012 at 2:59 pm

    #1505860

    Autogrow. I had created a DMP to update statistics and rebuild index. Thats when db grew.

  • June 26, 2012 at 3:09 pm

    #1505865

    SQL_Surfer (6/26/2012)

    Autogrow. I had created a DMP to update statistics and rebuild index. Thats when db grew.

    Thanks for the response. You said you were in FULL recovery mode. In between tran log backups, while your MP was executing, it sounds like your tran log may have bloated. If you shrink the log and keep your MP the log growth is sure to happen again. If you're stuck with what you have you have some options, but none of them are very good. If you can add more disk space that's what I would do.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 26, 2012 at 3:39 pm

    #1505880

    Are your backups going to a different drive than the one that's running out of space? Probably a silly question, but just wanted to make sure it wasn't your backups that were eating the space.

    What type of disk are you using" direct attached or SAN?

    And then again, I might be wrong ...
    David Webb

  • June 26, 2012 at 3:54 pm

    #1505892

    Since everything has been normal except your re-build of index. DB size growth might be expected since a rebuild requires space. It is also is dependent if you were using TEMPDB

    If no TEMPDB was not set On for re-build. Then it will use space within the Database.

    Space requirements are different

    - Clustered Index rebuild (shifts data, and indexes around .. you need space...min size of the whole table but likely will use more to shift data around)

    -Non clustered Index rebuild (just the size of index is needed space wise)

    Since you're done with your operation. You may be able to re-size back your database a touch. Assuming there's lots of empty space within the DB.

    example

    db size plus sign is clustered index

    [||||||||+++]

    db size during re-build expanded a copy of clustered index being re build (extra ***)

    [||||||||+++][***]

    ideally afterwards db size after re-build expanded zeros 0 are emtpy

    [||||||||||||][000]

    Hopefully SQLServer found space at end of your datafile and it's empty and other things didn't "take up space"

    [||||||||||00][0||]

    (then no re-size)

    Then that's not a good an outcome 🙁

    I could be wrong, but just my thoughts...and there's all kinds of "no no's" with re-sizing, etc... I think all things are "it depends"

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • June 26, 2012 at 4:01 pm

    #1505898

    From what you have said so far it sounds like log file bloat is your problem, and in particular this was caused by an index rebuild.

    If extra disk space is a problem or cannot be obtained quickly there are a number of options available to you to reduce log growth caused by index rebuilds

    don't use a maintenance plan which rebuilds all indexes whether they need it or not, use a script that only rebuilds indexes above certain fragmentation thresholds

    split the rebuilds up so you do different tables at different times

    backup the log more frequently during the rebuilds (an option but often the least successful)

    put the database into bulk recovery mode before the rebuild, and back to full afterwards. Note this means you lose point in time recovery during that period and although the amount of logging will be minimal backups of the log will be large.

    If backups are also on this drive do you have a version of 2008 that allows backup compression?

    ---------------------------------------------------------------------

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply