Primary FileGroup Fill

  • Hi All,

    Have a database which has 4 different datafiles as part of the Primary filegroup. These 4 files are on 4 different drives. 3 of these drives are fill. The 3 datafiles on these drives have all been set to AutoGrowth = None.

    The 4th file on the 4th drive has about 200 GB free and has AutoGrowth enabled.

    Recently my index job has been failing with the following reason : Could not allocate space for object 'ObjectName' in database 'DatbaseName' because the 'PRIMARY' filegroup is full

    The index is currently sitting in a file that's on a drive with no space. I assumed that even though that drive is fill...with the creation of another datafile on a different drive...that new file would be used for more space for that index.

    Does this then mean the index needs to be dropped and recreated on this new file. If so...how do you specify the file name you want the index to be created on

    Thanks

    Denesh

  • Are there any errors in the Error Log? Perhaps the 4th file has no free space and it set to grow by a percentage (10% of huge = big) and the growth is 'timing out'. If this happens, SQL Server puts an entry in the Error Log after failing to grow with the number of milliseconds waiting before giving up. Typically, you'll see several of these entries before getting a Severity 17 or other error.

    This is just a stab at the problem, but if it is the problem, grow out your data file by a large amount so that an auto growth does not occur in the next year or so (this will impact system performance if Instant File Initialization is not enabled).

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • Are you sure these are separate files in the Primary and not separate filegroups?

  • Hi Jim,

    Thanks for the reply.

    No other errors in the logs except the one for same error that the index job is returning. The 4th file has only 1GB free space. I have increased it to about 20GB. The index size on the table that is failing is 4GB. Hoping the 20GB is enough.

    Thanks...will check in Monday if the change was successful

    Denesh

  • Hi Steve,

    Yes...definitly sure they are seperate files in the Primary file group.

    Thanks

    Denesh

  • Hi All,

    Increasing the free space on the data file didn't work. I believe the reason ... because the index is a Primary Key Clustered index, the rebuild occurs on the data file that it currently exists on. Due that file being filled...the rebuild fails.

    The only solution would be to drop the index and recreate it on the new data file on the drive with space.

    Your thoughts would be appreciated.

    Denesh

  • Denesh Naidoo (6/8/2011)


    ... the rebuild occurs on the data file that it currently exists on. Due that file being filled...the rebuild fails.

    The only solution would be to drop the index and recreate it on the new data file on the drive with space...

    Try

    ALTER INDEX ... REBUILD WITH (SORT_IN_TEMPDB = ON)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply