none
The Space available of database is very high. RRS feed

  • Question

  • Dear Experts,

    This is to inform you that i am facing an issue with one of my database which is having total size of 336 gigs and space available is 127 Gigs(Property Of DB). I am little bit confused about the space available for the mentioned database which is taking 127 GB. The auto growth of the db is default 1 MB and have set the fill factor to default 80 for all the index.

    When i shrinks the same database , the space available has got freed up and that space has been allocated for windows .
    Due to this the mdf size of the database is also reduced from 336 gigs to 209 gigs. but i have configured a maintenance job for rebuilding indexes. but after rebuilding has done,the database size again reaches to 336 gigs and again space available reaches to 127 gigs.

    i have set the auto growth for Data File by 1 gigs but this couldn't help me.

    So how can i reduce the space available for the database and freed up that spaces for OS. i can do this operation by shrinking the database but it is not good habit to shrink database on daily basis.
    So please suggest me any other Option.


    Below are my sql server and windows server configuration details:
    Sql Version- Sql Server 2008 SP 3 Std Edition 64 bit
    OS Version- Windows Server 2008 R2 Enterprise


    Saturday, October 10, 2015 10:43 AM

Answers


  • Due to this the mdf size of the database is also reduced from 336 gigs to 209 gigs. but i have configured a maintenance job for rebuilding indexes. but after rebuilding has done,the database size again reaches to 336 gigs and again space available reaches to 127 gigs.




    This is expected behavior when you have large tables.  The space requirement to rebuild the clustered index is about 120% of the size of the original table.  So if you have a 100GB table, you need to maintain about 120GB unused space for maintenance.  Don't shrink the database (especially with traditional spinning media) as that will introduce fragmentation and reduce scan performance.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 10, 2015 12:54 PM
    Moderator
  • So i guess you can reproduce what I mentioned. But your question is about free space which is normal after you rebuild large index. Due to intermediate index which is ultimately deleted the space is there and can be reclaimed by shrinking


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, October 12, 2015 1:58 PM
    Moderator
  • As everyone has already indicated, this is completely expected behavior.  The server instance is responding to your direct actions.  If you want to rebuild the indexes, you will need sufficient space.  Rebuilding all indexes is not a best practice; a little searching on your part will find many MANY suggestions for reindexing in a "smart" fashion. 

    i can do this operation by shrinking the database but it is not good habit to shrink database on daily basis.

    There is no alternative except to change your indexing strategy. And it is futile to keep shrinking the database after every reindex.

    Monday, October 12, 2015 2:55 PM

All replies

  • Have you tried changing your fill factor?  Shrinking your database will fragment your database and when the indexes are rebuilt they will use the default fill factor.  You will also need additional space for index operations as well like sorting.

    What is the size of your transactional log and how much of it is being used?  Is it possible that your database size is big because you have a very big transactional log file that is mostly free? If so, you could just shrink the transactional log and skip the index rebuilds after shrinking the log file.

    DBCC SQLPERF(logspace)

    I would consider using an non-default fill factor for each index as they all shouldn't be treated equally. For example, an index on a uniqueidentifier should have a lower fill factor to allow more free space per page than an identity index that would insert into the last page which would cause good page splits.

    The following is a query that will help you get the index size. If free space is a priority to you I would rebuild the higher indexes with the highest fill factor as possible.

    SELECT

    OBJECT_NAME(i.OBJECT_ID)ASTableName,

    i.name ASIndexName,

    i.index_id ASIndexID,

    8 *SUM(a.used_pages)AS'Indexsize(KB)',

    (8 *SUM(a.used_pages))/1024.00 AS'IndexSize(MB)',

    (((8 *SUM(a.used_pages))/1024.00)/1024.00)AS'IndexSize(GB)',

    f.name ASFileGroupName

    FROMsys.indexesASi

    JOINsys.partitionsASp ONp.OBJECT_ID=i.OBJECT_IDANDp.index_id =i.index_id

    JOINsys.allocation_unitsASa ONa.container_id =p.partition_id

    JOINsys.filegroupsf ONf.data_space_id =i.data_space_id

    GROUPBYi.OBJECT_ID,i.index_id,i.name,f.name

    ORDERBY8 *SUM(a.used_pages)desc,OBJECT_NAME(i.OBJECT_ID),i.index_id

    The following will also give you a good example of space needed for index operations as well.

    https://msdn.microsoft.com/en-us/library/ms191183.aspx

    Saturday, October 10, 2015 12:36 PM

  • Due to this the mdf size of the database is also reduced from 336 gigs to 209 gigs. but i have configured a maintenance job for rebuilding indexes. but after rebuilding has done,the database size again reaches to 336 gigs and again space available reaches to 127 gigs.




    This is expected behavior when you have large tables.  The space requirement to rebuild the clustered index is about 120% of the size of the original table.  So if you have a 100GB table, you need to maintain about 120GB unused space for maintenance.  Don't shrink the database (especially with traditional spinning media) as that will introduce fragmentation and reduce scan performance.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 10, 2015 12:54 PM
    Moderator
  •  but i have configured a maintenance job for rebuilding indexes. but after rebuilding has done,the database size again reaches to 336 gigs and again space available reaches to 127 gigs.

    i have set the auto growth for Data File by 1 gigs but this couldn't help me.

    This is something not unusual specially when you use Online index rebuild. In index rebuild index is dropped and recreated and mean while a transient copy is maintained internally when this intermediate copy is deleted the space would not be released so this could be reason for space growing back to original value. You must also read

    Database size increases after index rebuild

    There was bug in SQL Server 2008 SP3 where when RCSI is enabled on database and you do online index rebuild then DB size increases significantly. I strongly suggest you to apply SP4.

    I also dont like 1MB autogrowth please change it to sensible value.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Saturday, October 10, 2015 3:08 PM
    Moderator
  • Dear Shanky ,

    I have Suggested to the Ops Team for updating the SP4 , for a time being i have created same scenario in UAT environment With 18 gigs of database, and RCSI is disabled for the same database. I use the below script to check the RCSI status

    select is_read_committed_snapshot_on, snapshot_isolation_state_desc,
    snapshot_isolation_state from sys.databases where name='Mydb'

    Output

    is_read_committed_snapshot_on -- 0 

    snapshot_isolation_state_desc -- OFF

    snapshot_isolation_state -- 0

    This time uat SQL Server version is : 

    Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) 
    Aug 19 2014 12:21:34 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    and the SP 3 is the last hot fix for Developer Edition

    While i rebuilds all the indexes with below attributes, the only Data file is continuously growing and the log file size is same as previously it was.

    FILLFACTOR = 80 STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF.

    The total growth of data file after rebuild - 4 GB

    The total growth of Log File file after rebuild -  0


    Monday, October 12, 2015 1:53 PM
  • So i guess you can reproduce what I mentioned. But your question is about free space which is normal after you rebuild large index. Due to intermediate index which is ultimately deleted the space is there and can be reclaimed by shrinking


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, October 12, 2015 1:58 PM
    Moderator
  • As everyone has already indicated, this is completely expected behavior.  The server instance is responding to your direct actions.  If you want to rebuild the indexes, you will need sufficient space.  Rebuilding all indexes is not a best practice; a little searching on your part will find many MANY suggestions for reindexing in a "smart" fashion. 

    i can do this operation by shrinking the database but it is not good habit to shrink database on daily basis.

    There is no alternative except to change your indexing strategy. And it is futile to keep shrinking the database after every reindex.

    Monday, October 12, 2015 2:55 PM