none
Database Properties

    Question

  • I am trying to find information on Database properties - more specifically, what the 'Space Available' means, because right now when I look at the properties of a database I am setting up, which will grow substantially I am sure, it says the database size is 154 MB and the Space Available is 42 MB. What is the Space Available? What does this refer to and is it going to be restricting the growth of the database? Thanks.

    Tuesday, March 04, 2008 2:24 PM

Answers

  • In the properties the size of the database is the amount of space that is reserved for the database.  The space available is the difference between the space reserved and the space occupied.  The deciding factor on the database growth is if you have set your files to autogrow and how this is configured.  From within the properties look at the files and the autogrow options.  as the database begins to require more space than has been reserved the autogrow will dictate how space is allocated, if at all.

     

    Tuesday, March 04, 2008 2:34 PM

All replies

  • Welcome to MSDN Forums Smile

    If you right click on your database and click on the general tab it displays the option space available which is the same as when you run sp_spaceused against your database. Space available refers to available free space in your database. It means in your db there is 42 MB of free space.

    - Deepak
    Tuesday, March 04, 2008 2:32 PM
  • In the properties the size of the database is the amount of space that is reserved for the database.  The space available is the difference between the space reserved and the space occupied.  The deciding factor on the database growth is if you have set your files to autogrow and how this is configured.  From within the properties look at the files and the autogrow options.  as the database begins to require more space than has been reserved the autogrow will dictate how space is allocated, if at all.

     

    Tuesday, March 04, 2008 2:34 PM
  • So if I have the autogrowth set to 'By 1MB Unrestricted Growth', I should not have a problem here at all? Or do I need to change/increase the reserved space on this database?

    Thanks.

    Tuesday, March 04, 2008 2:43 PM
  • Autogrowth should be based on the growth of you database and weighed agains any restrictions in storage availability.  Performance degradation can occur if there are repeated calls "autogrowing" the database.  You can refer to the included kb.

     

    Best of Luck!!

     

    http://support.microsoft.com/kb/315512

    Tuesday, March 04, 2008 2:49 PM
  • Thanks. So if I understand this correctly then, if the Space Available starts to decrease, I should increase the autogrowth to maybe 1/5 of the file size rather than 1/10?

    Thanks.

     

    Tuesday, March 04, 2008 3:09 PM
  • The autogrowth parameters should be set to allow reasonable growth, enough space to alleviate immediate future calls, but no so much as drain your storage resources.  It is very difficult to give a hard and fast rule or ratio as it is completely dependant upon your environment and keep in mind that autogrowth is set for each filegroup, log file (.ldf), primary file (.mdf), and any secondary file groups, if present (.ndf) so you must evaluate each separately based on the objects that they contain.  The below link is a blog by Kimberly Tripp in which she discusses the transaction 8 Steps to better Transaction Log throughput and goes into some detail about autogrowth specific to the transaction log.

     

    http://www.sqlskills.com/blogs/kimberly/default,month,2005-06.aspx

     

    Tuesday, March 04, 2008 4:09 PM