This is one of the famous error which has been reported many times by many clients. Here is the exact error which you might see. One of my clients complained that our database was unable to grow despite available space on the disk. Here is the error which they shared. Let us learn about error related to allocate space.
Could not allocate space for object ‘TableName’ in database ‘DB’ 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.
Here are the steps to reproduce the issue. Make sure you have a folder called Temp in C drive.
CREATE DATABASE [SQLAuthDB] ON (NAME = N'SQLAuthDB', FILENAME = N'C:\Temp\SQLAuthDB.mdf' , SIZE = 8192KB , MAXSIZE = 10240KB ) LOG ON (NAME = N'SQLAuthDB_log', FILENAME = N'C:\Temp\SQLAuthDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 10240KB ) GO
Once database is created, you can run below script to reproduce the error.
Here is the text of the message
USE [SQLAuthDB] GO CREATE TABLE ThisTable(i int, j char(8000)) GO SET NOCOUNT ON GO INSERT INTO ThisTable VALUES (1,'SQLAuthority') GO 961
Please note that there is digit 961 after the GO. It is not an error. It means the previous statement will run 961 times.
Msg 1105, Level 17, State 2, Line 7
Could not allocate space for object ‘dbo.ThisTable’ in database ‘SQLAuthDB’ 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.
Now, let look at database properties and you would find the answer.
Whenever you get such error check below.
- Check if auto growth is restricted.
- Check if auto growth is set to a high value which would cause disk space to become full.
- Check if disk space is low.
- Check SQL edition because SQL Express has a database file size limit.
Based on any of the above situation, you need to take corrective action. Here is the command to set the growth to UNLIMITED.
[sq]USE [master] GO
Cleanup of demo
Below are the steps to clean up the demo database created earlier.
USE [master] GO ALTER DATABASE [SQLAuthDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO DROP DATABASE [SQLAuthDB] GO
Have you faced a similar error? What was the solution you implemented?
Reference: Pinal Dave (https://blog.sqlauthority.com)
I’m having the issue right now on one of our production DBs in SQL Server 2008. The problem is, AutoGrowth is set to 10% Unrestricted, there is plenty of room on the HD and we have plenty of other servers with similar DBs that are far greater in size, including one on this server.
Do you have any clue what else might be limiting it?
P.S. I’m a big fan of your work and you’re one of the first places I turn to when I have an issue.
I also have the same issue plz give me feedback
I face same issues to add .ndf file.
After that I found auto growth is restricted. I set auto growth unrestricted and issues resolved
We found that modifying the local file permissions in Linux to chmod 755 %filename% was effective in relieving the error once the SQL Mgmt. settings were confirmed, SQL mgmt settings were set to auto grow @ 64MB/Unlimited.
incidentally, the volume was only 30% used
As of now, I have no idea about SQL On Linux.
Can I apply the same solution for sql express? Is there any limitation on sql server express to have multiple mdf files ?
Thank you so much for this, I would have never thought about it..you saved my Day!
appreciate the help.