June 3, 2011 at 8:06 am
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
June 3, 2011 at 8:24 am
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
June 3, 2011 at 8:47 am
Are you sure these are separate files in the Primary and not separate filegroups?
June 3, 2011 at 8:47 am
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
June 3, 2011 at 8:48 am
Hi Steve,
Yes...definitly sure they are seperate files in the Primary file group.
Thanks
Denesh
June 8, 2011 at 1:22 am
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
June 8, 2011 at 2:50 am
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