Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Rebuild


Index Rebuild

Author
Message
Hariharan R
Hariharan R
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
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.
vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 242
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.



Hariharan R
Hariharan R
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search