none
SQL Database initial size

    Question

  • Hi,

    I need your Advice.

    Details as fallows: Existing DB setting in Production DB (SQL Server 2005)

    Total Database size: 316686.25 MB (Sql Management Studio-->Database-->Properties--General)

    Space Available:  118.13 (Sql Management Studio-->Database-->Properties--General)

                                        Initial Growth         Auto Growth

    Initial Growth : .mdf -->139,993 MB           By 10 MB, unrestricted growth     D:\    Free space 92 GB

                             .ndf -->6585 MB                By 10 MB, unrestricted growth     E:\     Free space 57 GB

                             .ndf-->166911 MB             By 10 MB, unrestricted growth     F:\     Free space 12 GB    (This files is growing rapidly)

    My database growth is per month around 10-12 GB.

    Please advice me that is it necessary to increase Space Available or the existing 118.13 is sufficient or not.


    Monday, October 22, 2012 8:20 AM

Answers

  • Hi,

    That's 118MB, right?  If you've got 10-12GB data going into your database per month, and most of that is going on to your F: drive with 12GB free, then yes, you do have an issue.  Regardless of what the autogrowth setting is, in about 5 or 6 weeks (I would guess), you're going to run out of space on F:.

    You either need to create a new filegroup on a disk with sufficient space and move the tables over (you can do this by recreating the clustered index on the new filegroup, with drop existing), or see if there's any pruning that can be done in order for you to reclaim disk space.

    Personally, I would also manually grow out the files to provide a bit of breathing room, so that SQL Server doesn't do it automatically.  For a 316GB database with 10-12GB monthly growth, 10MB growth increments seems a tad small too.  You'll likely introduce physical fragmentation if it needs to grow (which it sounds like it's about to do).



    Thanks, Andrew

    Tuesday, October 23, 2012 8:33 AM
  • Tuesday, October 23, 2012 9:09 AM
  • space available on disk, or space available in the data file?  The data file will autogrow in 10MB increments until the capacity of the disk is reached.  Once you hit the disk limits, then you'll start to get errors about data files being full.


    Thanks, Andrew

    Tuesday, October 23, 2012 9:28 AM

All replies

  • Hi Vijay,

    As your database is set to Autogrowth by 10 MB, unrestricted growth, as long as you have sufficient physical disk space, the current Space Available for the database is sufficient.


    Best Regards,
    Ray Chen

    Tuesday, October 23, 2012 7:55 AM
    Moderator
  • Hi,

    That's 118MB, right?  If you've got 10-12GB data going into your database per month, and most of that is going on to your F: drive with 12GB free, then yes, you do have an issue.  Regardless of what the autogrowth setting is, in about 5 or 6 weeks (I would guess), you're going to run out of space on F:.

    You either need to create a new filegroup on a disk with sufficient space and move the tables over (you can do this by recreating the clustered index on the new filegroup, with drop existing), or see if there's any pruning that can be done in order for you to reclaim disk space.

    Personally, I would also manually grow out the files to provide a bit of breathing room, so that SQL Server doesn't do it automatically.  For a 316GB database with 10-12GB monthly growth, 10MB growth increments seems a tad small too.  You'll likely introduce physical fragmentation if it needs to grow (which it sounds like it's about to do).



    Thanks, Andrew

    Tuesday, October 23, 2012 8:33 AM
  • no there is no need to increase the Disk space, Space Available is the free space available in your database,database growth per month depends upon the database processed in your database,please check the log file(ldf) size of your database.by running

    DBCC SQLPERF(LOGSPACE)

    --gives the information about your log(ldf) file size details


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by vr.babu Tuesday, October 23, 2012 10:04 AM
    • Unproposed as answer by vr.babu Tuesday, October 23, 2012 10:04 AM
    • Proposed as answer by vr.babu Tuesday, October 23, 2012 10:04 AM
    • Unproposed as answer by vr.babu Tuesday, October 23, 2012 10:04 AM
    • Edited by vr.babu Tuesday, October 23, 2012 12:29 PM
    Tuesday, October 23, 2012 8:52 AM
  • Hi,

    Thank you very much.

    Now my plan is, there is a big table in F:/ drive. can i split that table and move to new .ndf file. If yes, Please explain the procedure.Because i have added one more drive called G:/  which contains 100 GB of Free diskspace.

    Tuesday, October 23, 2012 8:57 AM
  • Tuesday, October 23, 2012 9:09 AM
  • Hi,

    What if Space Available becomes '0'? then how to resolve this?

    Tuesday, October 23, 2012 9:24 AM
  • space available on disk, or space available in the data file?  The data file will autogrow in 10MB increments until the capacity of the disk is reached.  Once you hit the disk limits, then you'll start to get errors about data files being full.


    Thanks, Andrew

    Tuesday, October 23, 2012 9:28 AM
  • I am taking about space available in the data file (Sql Management Studio-->Database-->Properties--General) now it is 80 Mb. But space available in disk is 10 GB.

    Tuesday, October 23, 2012 10:07 AM