12

Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such:

Data_file1    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file2    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file3    PRIMARY    0 growth         Less than 1% free      Max Size UNLIMITED
Data_file4    PRIMARY    250 MB growth    Less than 1% free      Max Size UNLIMITED

Essentially, 3 of the 4 data files are full and not allowed to grow, the fourth is full and allowed to grow. The files are spread out across different LUNs (and the reason for why is messy). So when the online index rebuild starts, it is my understanding that if any additional space is needed, it will grow into Data_file4 and be fine, but it is apparently trying to grow into a different file where growth is not allowed and failing. I am unable to reproduce this error, but I was wondering if anyone had insight into why this happens.

Full SQL Server version is 2008 R2 Enterprise, SP2 CU 4 (10.50.4270). We use Ola Hallengren's rebuild scripts, where we rebuild online but no sort in tempdb.

Improve this question
9
  • Is the max file size specified? The docs say If max_size is not specified, the file size will increase until the disk is full. Granted, if auto-growth is off, that shouldn't be trying to allocate from those files (A value of 0 indicates that automatic growth is set to off and no additional space is allowed.), but there may be a bug, so it wouldn't hurt to give it a try if it's not set.
    – Jon Seigel
    Commented Feb 26, 2014 at 17:57
  • max_size is currently set to UNLIMITED, even on the ones that are 0 growth. I'm investigating this in my repro test right now.
    – Mike Fal
    Commented Feb 26, 2014 at 17:58
  • Are you logging the results? If you keep historical records does the error occur on the same index every time it fails?
    – Cougar9000
    Commented Feb 26, 2014 at 23:15
  • How many pages is the index in question?
    – Mark Wilkinson
    Commented Feb 27, 2014 at 14:56
  • Also, is this an error generated by the script, or an actual SQL Server error? I ask because I wonder if maybe you are hitting a transaction log size limit, as opposed to a data file size limit, and the script is handling the error incorrectly.
    – Mark Wilkinson
    Commented Feb 27, 2014 at 15:06

1 Answer 1

Reset to default
1

My experience is that it is always going to do an online rebuild in the filegroup on which the index lives. It has to map the existing index and hold enough space for, essentially, one copy.

You should only be getting the error when an index which is too large to hold mappings (the copy) is rebuilt - for instance, one time it may be fragmented enough to qualify in Ola's script and the next time it may not be.

There is a great article http://technet.microsoft.com/en-us/library/ms179542(v=sql.105).aspx which I had to read several times when running into disk space issues with indexes.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.