I am confused or do not have better understanding on how SQL works in below scenario and need some views:
For a database X, we have a FILEGROUP named FG which has 4 data files tagged to it as below
1. FG_data1.ndf which is restricted to 200 GB and current free space is 0 GB 2. FG_data2.ndf which is restricted to 200 GB and current free space is 10 GB 3. FG_data3.ndf which is restricted to 200 GB and current free space is 0 GB 4. FG_data4.ndf which is restricted to 200 GB and current free space is 60 GB
Apart from above we have PRIMARY FG with mdf data file set to unrestricted growth. All are set to auto grow by 10 GB
Now, i see alert for Filegroup FG being FULL and rebuild index job failing with same saying "could not allocate space for object within the table tb1 for the database above because the Filegroup is FULL"
But if i see from above data files tagged to FG, we still have 70 GB left, then how come its failing ?
Please suggest how SQL works here in allocating new data for case above?
This tb1 is partitioned and have 366 days of partitions for yearly defined scheme
when queried for sys.dm_db_index_physical_stats i see Clustered index listing for each partition and some have avg frag value over 50 and some partition index below 30-20. How do i know whats going on in the background?
only 1 index that is clustered
name rows reserved data index_size unused tb1 433785377 445952328 KB 444075128 KB 1800504 KB 76696 KB