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