Primary FileGroup Fill

Question

Primary FileGroup Fill

Denesh Naidoo

SSCrazy

Points: 2348
More actions
June 3, 2011 at 8:06 am

#252088

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

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

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 2

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 3

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 4

Hi Steve,

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

Thanks

Denesh

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 5

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

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)