Post reply

databases file space utilization 100%?

  • January 10, 2012 at 11:26 pm

    #248827

    Hi All,

    Today i monitor my databases i found some of my databases are utilized 100% file space. What is the best solution to fix it,

    1) Shrink db or

    2) create new .ndf file.

    or any other solution?

    Thanks in advance.

    Regards,

    M.I

    ________________________________________
    M.I.
    [font="Times New Roman"]

    Learning is a path with no destination...
    [/font]

  • January 11, 2012 at 12:23 am

    #1431594

    You have not mentioned whether you have monitored the drive space.

    Is AutoGrow is ON?

    1) You can't shrink file, if 100% file space is utilized

    2) If you have free space is in the drive, no need to add new .ndf file.

    Additional notes:

    1) If AutoGrowth if ON, file will grow automatically.

    2) Shinking DB is NOT a best practice

    3) Pre-size your files.

  • January 11, 2012 at 12:45 am

    #1431599

    By file space do you mean space on the drive ( disk space) or the file has reached the max size for the file .

    You mentioned ndf , but most of the time the file that first reach the limit if the ldf file could u confirm which file your referring to .

    The answer could depend on which environment the database is hosted on. E.g if it was a dev enviornment you could get away with just shrinking the db. If its a prod enviornment you might want to visit your sizing or your backup plans and check the autogrowth and max file size properties for the database.

    Jayanth Kurup[/url]

  • January 11, 2012 at 8:59 am

    #1431869

    You don't want to shrink and that won't fix the issue if all the space in the file is used. If you have drive space you should grow the file. One thing you can do is use the backup history to determine how the DB is growing and then grow the file to an appropriate size so that it will not need to grow again for a best guess predetermined time based on history. A best practice is have your files set to autogrow a reasonable set MB as a backup to monitoring and growing it on a schedule based on % space available.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 11, 2012 at 5:17 pm

    #1432224

    MSQLDBA (1/10/2012)

    Hi All,

    Today i monitor my databases i found some of my databases are utilized 100% file space. What is the best solution to fix it,

    1) Shrink db or

    2) create new .ndf file.

    or any other solution?

    Thanks in advance.

    Regards,

    M.I

    What's using the space? The log file? What is the recovery mode for your databases and are you doing regularbackups?

    --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)

Viewing 5 posts - 1 through 4 (of 4 total)

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