March 29, 2010 at 2:26 am
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_sizeIndex space Datafiles nameLogfile nameTable name
Rectest2.6 GB660 MBRectest_dat and Rectest_dat1Rectest_logTest
-- 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.
March 29, 2010 at 2:52 am
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.
March 29, 2010 at 3:03 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply