locked
Unused Vs. Unallocated Space RRS feed

  • Question

  • To my knowledge, unallocated space is storage that has been allocated to the database, but not yet to a specific object. Unused space is reserved for existing tables(indexes too?) to grow in. I've read a few articles, and managed to confuse myself, so here is my question:

    When an autogrow happens, does it create unused space, or unallocated space?


    We do index defragmentation every night via a "Reorganize index" maintenance plan, but our DB has about 17 MB of unused space and 17 GB of unallocated space. I'm trying to determine if we need all this unallocated space.

    Thanks in advance.

    Clint
    Thursday, June 18, 2009 9:24 PM

Answers

  • >>When an autogrow happens, does it create unused space, or unallocated space?

    If I am NOT mistaken, the answer to your question is above.

    Unused space is specific to an object (table, index) and part of it may not be used yet.

    Unallocated space is free space, file/db has taken from OS but no objects (tables ,indexes) have claimed/asked for that yet.

    17 GB of unallocated space is NOT much, and you shouldn't worry about taking this space back if it is a PROD server. Based on your insert/update activity, I would be worried that there is only 17 GB left. You don't want the files/db to auto grow as a general rule but only as a fall back option.

    Hope I haven't confused you. If I am not correct, I hope some else will correct me.



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, June 18, 2009 11:52 PM
  • I definitely agree about your autogrow thoughts, Sankar (I am, after all, the writer of http://www.karaszi.com/SQLServer/info_dont_shrink.asp ;-) ).

    Here's another angle:

    Unallocated is extents that are not in use. I.e. extents that are up-for-grabs. In essence, these extents are maked with bit 1 in GAM page. Any type of grow will produce unallocated extents.

    Unused are pages on allocated extents, but thise pages are not yet used by any objects. As soon as an extent is allocated (either as uniform or shared extent), we have 8 reserved pages on that extent. Some pages are used, and some are unused.

    Whether 17GB unallocated is much depends on your database size. It is not uncommon to have some 30-50% of the database size as unallocated (SQL Server loves to have air to breathe).

    Here's a good section: http://msdn.microsoft.com/en-us/library/cc280360.aspx
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, June 19, 2009 7:28 AM

All replies

  • >>When an autogrow happens, does it create unused space, or unallocated space?

    If I am NOT mistaken, the answer to your question is above.

    Unused space is specific to an object (table, index) and part of it may not be used yet.

    Unallocated space is free space, file/db has taken from OS but no objects (tables ,indexes) have claimed/asked for that yet.

    17 GB of unallocated space is NOT much, and you shouldn't worry about taking this space back if it is a PROD server. Based on your insert/update activity, I would be worried that there is only 17 GB left. You don't want the files/db to auto grow as a general rule but only as a fall back option.

    Hope I haven't confused you. If I am not correct, I hope some else will correct me.



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, June 18, 2009 11:52 PM
  • I definitely agree about your autogrow thoughts, Sankar (I am, after all, the writer of http://www.karaszi.com/SQLServer/info_dont_shrink.asp ;-) ).

    Here's another angle:

    Unallocated is extents that are not in use. I.e. extents that are up-for-grabs. In essence, these extents are maked with bit 1 in GAM page. Any type of grow will produce unallocated extents.

    Unused are pages on allocated extents, but thise pages are not yet used by any objects. As soon as an extent is allocated (either as uniform or shared extent), we have 8 reserved pages on that extent. Some pages are used, and some are unused.

    Whether 17GB unallocated is much depends on your database size. It is not uncommon to have some 30-50% of the database size as unallocated (SQL Server loves to have air to breathe).

    Here's a good section: http://msdn.microsoft.com/en-us/library/cc280360.aspx
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, June 19, 2009 7:28 AM
  • Thanks for your replies, all.
    Clint
    Wednesday, June 24, 2009 9:46 PM