March 20, 2021 at 11:28 am
Hi Experts,
I got error on one of our production database.
Could not allocate space for object 'dbo.'.'' in database '' 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.
The database got 3 files in the Primary filegroup and two are restricted in size but the third file is newly created in a disk with enough space to grow(attached screesnshot). Still why this error?
TIA
March 21, 2021 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 22, 2021 at 2:51 pm
The first two are not full but don't have room to expand by the specified amount.
Alter the first two files to have a maxsize of their current size. That will prevent SQL from attempting to add space to either of those files.
ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_1], MAXSIZE = 389294MB )
ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_2], MAXSIZE = 225792MB )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2021 at 5:17 am
The first two are not full but don't have room to expand by the specified amount.
Alter the first two files to have a maxsize of their current size. That will prevent SQL from attempting to add space to either of those files.
ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_1], MAXSIZE = 389294MB )
ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_2], MAXSIZE = 225792MB )
Thanks Scott. Will the operation fail because of the existing setup or it will try using the new file?
March 29, 2021 at 1:59 pm
Should use the new file just fine. Make sure the new file is present, or you will have no space to write to.
What's happening is this. Your file has not reached its max, so SQL tries to grow the file .., but the amount of disk remaining on that drive is not enough to grow as much as you specified, so the growth fails.
By setting the max size of the file to its current size, SQL will never try to grow that file, since it has reached its max size, avoiding this error.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2021 at 8:48 am
Should use the new file just fine. Make sure the new file is present, or you will have no space to write to.
What's happening is this. Your file has not reached its max, so SQL tries to grow the file .., but the amount of disk remaining on that drive is not enough to grow as much as you specified, so the growth fails.
By setting the max size of the file to its current size, SQL will never try to grow that file, since it has reached its max size, avoiding this error.
Thanks Scott, I think I didn't make my question clear. If I didn't set the file size as you suggested will the transaction fail after the error or it will succeed by using the available file.
May 22, 2021 at 10:41 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply