First chance at a new job to do some much needed index maintenance on a system.

2-node cluster, W2K12R2, 512GB RAM, 64 logical cores, Fusion-IO, NetApp

SQL Server 2012 Ent SP1

3.5TB db

4 filegroups spread across 91 files.

4 of the 91 files were added 3 days ago to the filegroup called C below.

The 4 new files all show ONLINE in state_desc of sys.master_files

Very heavy CRUD operations 24/7- no complaints or tickets from NOC.

sp_whoisactive does not show any blocking or anything out of the ordinary.

RDP into active node and server shows plenty of disk space is available.

No major entries in SQL log file except the error(s) below.

No entries in msdb.dbo.suspect_pages

Other defrag operations on other small tables has worked fine.

When trying to defragment a 14MB table using reorganize against the CI, I receive the following and can reproduce the problem:

Error: 1105, Severity: 17, State: 2

Could not allocate space for object 'A' in database 'B' because the 'C' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the file group.

Any ideas? Thanks! --Todd

  • 1
    What are the autogrowth settings for each of the files? (I don't want all 91, but are they all consistent?) There have been multiple scenarios where autogrowth gets pushed way out of whack (like 6 figure %) and so "plenty of free space" will never be enough. Also, are you sure this error is initiated during a REORG and not a REBUILD? – Aaron Bertrand Nov 6 '15 at 21:53
  • It appears outgrowth is currently set to none across the board, all files are fixed 40GB in size. – Todd Kleinhans Nov 6 '15 at 21:59
  • So 87 files are completely full to 40GB, and there are 4 empty files? How have you validated that there is space free in the 4 new files? (I know some of this has been discussed on twitter but I'm trying to prompt you to update the question so all of the info is self-contained.) – Aaron Bertrand Nov 6 '15 at 22:00
  • dba.stackexchange.com/questions/29543/… shows lots of Available Space In MB on the new files – Todd Kleinhans Nov 6 '15 at 22:12
  • Tried to reproduce the issue after this past weekend. No problems- index reorganize succeeded this morning. I can see space is being used now in the new files via the link above (Glenn Berry's Query 46 File Sizes and Space DMV 2012). Will move on to other indexes to be sure. – Todd Kleinhans Nov 9 '15 at 15:13

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.