Post reply

Database growth

  • December 9, 2008 at 6:26 pm

    #72264

    Hi,

    I have database ABC. It has 4 datafiles and 2 log files. Inorder to maintain database growth, we are removing Autogrowth option(as keeping datafile growth automatic is not a good option) and decided to keep 15% free space for datafiles. So now Iam going to do this manually.

    Here Iam going to follow below proceudure and I have couple of questions at the end:

    1.I ran sp_spaceused:

    SP_SPACEUSED

    results

    Database_name Database_size Unallocated space

    ABC 32857.75 MB-141281.45 MB

    Reserverd data index_size unused

    178246096 KB25233912 KB151644456 KB1367728 KB

    2.I used the below script to know the actual datafile n logfile size and free space.

    sp_msforeachdb 'USE ?

    select Name,

    (convert(float,size)) * (8192.0/1048576) File_Size,

    (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576) MB_Used,

    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576)) MB_Free

    from sysfiles

    order by

    fileproperty(name,''IsLogFile'')'

    Results:

    NAME File_size MB_USED MB_FREE

    ABC1_DAT 11322 11312 10

    ABC2_DAT 3333 2501 831

    ABC3_NDX 3450 2589 861

    ABC4_NDX 14680 14669 10

    ABC1_LOG 1 0.08 0.92

    ABC2_LOG 26 2 24

    Questions:

    1.In step 1 why its showing unallocated space as negative -141281.45 MB

    2.In enterprise manager when I see the database properties its showing database size as 32857 MB and space available as '0'MB. But from step 2 results,its showing free space available (MB_FREE) for each individual data file.

    So why In enterprise manager,database properties its showing available space as zero?

    and Iam going to increase the available space in each datafile to 15% like below

    NAME AVailable space =(filesize*.15-MB_FREE)

    ABC1_DAT (11322*.15-10)

    ABC2_DAT (11322*.15-10)

    ABC3_NDX (3450*.15-861)

    ABC4_NDX (14680 *.15-10) is this correct approach? or do we need to increase available space as database size *15%?

    Plz advice me

    Thank You

  • December 9, 2008 at 7:36 pm

    #910030

    Run DBCC UPDATEUSAGE and see if that get's rid of the negative numbers.

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

  • December 9, 2008 at 8:29 pm

    #910039

    Thanks Jeff,

    Thank you

    After running dbcc spaceused, I ran the sp_spaceused now there is no negative numbers

    SP_spaceused

    Results:

    Database_name database_size Unallocated space

    ABC 32857.75 MB 1706.06 MB

    reserver data index_size unused

    31826880 KB25737192 KB5218304 KB871384 KB

    Could you plz suggest me the way Iam going to do manual growth

    as below

    NAME AVailable space =(filesize*.15-MB_FREE)

    ABC1_DAT (11322*.15-10)

    ABC2_DAT (11322*.15-10)

    ABC3_NDX (3450*.15-861)

    ABC4_NDX (14680 *.15-10

  • December 9, 2008 at 9:30 pm

    #910054

    This isn't an exact science and your data sizes will change constantly. You don't want to be updating space every day or every week.

    Set enough space in the data files to last a month or two, and then check it again and set the sizes larger depending on growth. Unless you've been tracking space, it doesn't matter which what you set the space to. Go 15% of so larger than what space appears to be now.

    For log files, you need enough space to handle the log between log backups. Your backup scheme will give you an idea here. Look at log backups, then add some pad (15% should be fine) and set the log files this big.

  • December 9, 2008 at 9:46 pm

    #910065

    Heh... I say, stop messing with it. Sample the size of the used portion for a month, use that determine the growth rate for 12 months, set it to that, an forget it.

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

  • December 10, 2008 at 12:25 am

    #910105

    Hi Jeff,

    Iam curious to know why the available space for the database was showed as zero in enterprise manager and negative value when I ran sp_spaceused before running DBCC UPDATEUSAGE.

    After running DBCC UPDATEUSAGE when I see the database properties in enterprise manager now the database is showing 1718MB.

    and do we need to run DBCC UPDATEUSAGE for all the remaining databases?

    Plz clarify me

    Thanks again

  • December 10, 2008 at 1:02 am

    #910114

    madhu.arda (12/10/2008)

    Hi Jeff,

    Iam curious to know why the available space for the database was showed as zero in enterprise manager and negative value when I ran sp_spaceused before running DBCC UPDATEUSAGE.

    SQL 2000 didn't always keep the space used and space free metadata correct. It can lead to this kind of thing. It's fixed in SQL 2005, but if you're on SQL 2000, you may need to run updateusage from time to time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 10, 2008 at 1:04 am

    #910118

    madhu.arda (12/10/2008)

    and do we need to run DBCC UPDATEUSAGE for all the remaining databases?

    [font="Verdana"]Run it. Schedule it as a weekly job if you are monitoring space to make adjustments for growth manually.[/font]

    -Hope is a heuristic search :smooooth: ~Hemanth

  • December 10, 2008 at 1:18 am

    #910119

    Other question: Why do you have 2 logfiles?

    Unlike datafiles, logging is not spreadded over 2 files. It's just filling logfile 1 before using logfile 2

    Wilfred
    The best things in life are the simple things

  • December 10, 2008 at 6:03 am

    #910267

    madhu.arda (12/10/2008)

    Hi Jeff,

    Iam curious to know why the available space for the database was showed as zero in enterprise manager and negative value when I ran sp_spaceused before running DBCC UPDATEUSAGE.

    I could explain it... but I'd just be regurgitating what's in Books Online. Like I said before, read up on it there.

    After running DBCC UPDATEUSAGE when I see the database properties in enterprise manager now the database is showing 1718MB.

    That's the more accurate number.

    and do we need to run DBCC UPDATEUSAGE for all the remaining databases?

    heh... at this point, what do you think? 😉 I do it routinely as part of a maintenance plan.

    --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 10 posts - 1 through 9 (of 9 total)

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