Multiple Files in a FileGroup but still space issue error.

Question

Multiple Files in a FileGroup but still space issue error.

Reo

Hall of Fame

Points: 3774
More actions
January 4, 2018 at 12:32 pm

#409920

Hi All,
I have a database (40GB in size) on SQL Server 2012 SP3 (with all default setting & no addition file & file-groups)
One day Primary data file (D:\AdventureWork_data.mdf) got full... so I disabled auto growth of it and I created secondary file (E:\AdventureWork_data.ndf) in PrimaryFile Group.
Now issue is that I still getting Primary filegroup is full...where I have good amount of free space in Primary filegroup because I added secondary file in Primary filegroup which hosted on E: drive which having free space a lot and there is no restriction to grow.
How to stop SQL Engine allocating new pages/extents in a particular file in a filegroup?
Error:
DATE/TIME:xxxxx AM DESCRIPTION:Could not allocate space for object 'dbo.Resources'.'Resources_PK' in database 'SharePoint_Content_QA' because the 'PRIMARY' 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 filegroup.
Ram
MSSQL DBA

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

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Are you only getting this message during reindexing, perhaps?

It's a known-but-sparsely-documented issue with REORGANIZE that it can lead to this error even when you have plenty of space in another file in the filegroup.

If that's what's happening, it's probably easiest just to grow the full file by a bit if at all possible.

Cheers!

Reo Hall of Fame Points: 3774 More actions · Answer 2

Thanks for reply.
Simple Insert/Update raising error "PRIMARY" FileGroup full......BUT secondary file having space a lot. Not sure why SQL Storage engine keep trying to allocate space in 1st file and not trying in 2nd file which having space.

Ram
MSSQL DBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If there's a second file in primary, new pages should be allocated with proportional fill, meaning the full file gets no pages and the empty gets all new pages. you don't have to do anything, that's the default behaviour.

Can you please either script out the CREATE DATABASE, or upload a screenshot of the file/filegroup layout?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Reo Hall of Fame Points: 3774 More actions · Answer 4

Hi Gila

1st File which used space is 30GB and it having little free space around 10MB BUT "Enable AutoGrowth" in unchecked.
2nd File (on separate drive) which used space is 8GB size and its free space in 2GB and it allow to grow.

Thanks

Ram
MSSQL DBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GilaMonster - Thursday, January 4, 2018 1:06 PM
Can you please either script out the CREATE DATABASE, or upload a screenshot of the file/filegroup layout?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RandomStream Hall of Fame Points: 3735 More actions · Answer 6

Reo - Thursday, January 4, 2018 12:32 PM
Hi All,
I have a database (40GB in size) on SQL Server 2012 SP3 (with all default setting & no addition file & file-groups)
One day Primary data file (D:\AdventureWork_data.mdf) got full... so I disabled auto growth of it and I created secondary file (E:\AdventureWork_data.ndf) in PrimaryFile Group.
Now issue is that I still getting Primary filegroup is full...where I have good amount of free space in Primary filegroup because I added secondary file in Primary filegroup which hosted on E: drive which having free space a lot and there is no restriction to grow.
How to stop SQL Engine allocating new pages/extents in a particular file in a filegroup?
Error:
DATE/TIME:xxxxx AM DESCRIPTION:Could not allocate space for object 'dbo.Resources'.'Resources_PK' in database 'SharePoint_Content_QA' because the 'PRIMARY' 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 filegroup.

Would you please run this following query against your database and share the output? Thanks.

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],

CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size (MB)],

CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Free Space (MB)],

CAST (CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) * 100 / CAST((f.size/128.0) AS DECIMAL(15,2)) AS DECIMAL(5,2)) AS [% Free],

f.[File_ID], fg.name AS [Filegroup], f.is_percent_growth As [Growth By %], f.growth/128 AS [Growth Size (MB)], fg.is_default, fg.is_read_only

FROM sys.database_files AS f WITH (NOLOCK)

LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)

ON f.data_space_id = fg.data_space_id

ORDER BY f.[file_id] OPTION (RECOMPILE);

RandomStream Hall of Fame Points: 3735 More actions · Answer 7

I'm asking because the error message points to database 'SharePoint_Content_QA' and your mentioned adding a file to AdventureWork.