0

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?

Edit

tb1 info

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
  • How big is the index being rebuilt? – Sean Gallardy Mar 31 '18 at 17:03
  • @SeanGallardy-Microsoft- Just updated – BeginnerDBA Mar 31 '18 at 17:38
  • Your clustered index is the table, so you need that much space + sort space + extra in the FG (unless you choose sort in tempdb). Since you don't have that much free space available in the FG filegroup that it resides on, you run out of space. – Sean Gallardy Mar 31 '18 at 21:45
  • @SeanGallardy-Microsoft, thanks i also thought so. But with same table size, as i can compare this has hard;y grown, then how come the index maintenance job completed last weekend? – BeginnerDBA Mar 31 '18 at 22:45
  • @SeanGallardy-Microsoft: Just updated Q as the table is partitioned – BeginnerDBA Mar 31 '18 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.