Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Rebuild Expand / Collapse
Author
Message
Posted Monday, March 29, 2010 2:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 2:44 AM
Points: 243, Visits: 57
I just thought to give some practical explanation for the rebuild issue which happened in our environment.

These are details of Database Used:

DB_Name DB_size Index space Datafiles name Logfile name Table name
Rectest 2.6 GB 660 MB Rectest_dat and Rectest_dat1 Rectest_log Test


-- ALTER INDEX ALL ON Test REBUILD
-- PLEASE CHECK THE ATTACHED FILE FOR DATABASE DETAILS LIKE INDEX SPACE AND ETC.
-- NOTE : Only 3 MB free in Log out of 53 MB.

Scenario 1: Auto-growth disabled for data and log.

1. In this case Unused space is 2 MB.
2. When I ran the rebuild it failed because no space for growth “ Increase the primary file-group space”

Scenario 2: Auto-growth disabled for data and Unrestricted growth for log.

1. In this case also Unused space is 2 MB.
2. When I ran the rebuild it failed because no space for growth “ Increase the primary file-group space”.
3. As per best practice Log space should be 2.5 times the Index space.
4. In this case I’ve set it to auto-grow but still it failed.

Scenario 3: Auto-growth set for 1 Data file [Rectest_dat] and Disabled log growth.

1. In this case also Unused space is 2 MB.
2. But now the re-build took almost 10MIN to successfully complete.
3. Now the unused space changed from 2 MB to 1200 MB (i.e. double the Index space ).
4.
5. On successful completion the log space used is 2 MB and high percentage used is 54 %.

So, from this we have 2 options to go ahead
1. We have to increase the Data file space.
2. Set the Data files to auto grow during indexing and bring it back to restricted growth once rebuild completed.

These are my few suggestion from my side. Let me know if you need more info.
Please let me know if you find any mistake or assistance and your suggestion are fully accepted.
Post #891651
Posted Monday, March 29, 2010 2:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
Why are running so short of disk space?
If you have critical application, Recommend you to have proper disk space allocated as per the requirement to avoid such kind of time consuming procedures for regular database maintenance activity.



Post #891662
Posted Monday, March 29, 2010 3:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 2:44 AM
Points: 243, Visits: 57
In our environment we kept all database files to restricted growth. So what happened is the index size became 58 GB and the free space [UNUSED] was below 40 GB that is why we got this error.
It was working fine till last month rebuild.

Hence we came out with a solution to set both the data files to set Auto grow.
Post #891667
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse