Fix Error 1105: Could not allocate space for object in database because the filegroup is full.
Today, I got error 1105 on one of the database server. We were processing some bulk transactions on a database but failed because of space issue. Full description of error 1105 is given below.
Error 1105, Severity 17, State 2
Could not allocate space for object ‘Object_Name’ in database ‘DB_Name’ 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.
Error 1105 – Root Cause
As error suggests, this issue was generated because database was not able to allocate space anymore. Whenever you get such space issue, all transactions will be stopped until you make some room in the database. All transactions will be stopped because of non availability of space because none of the transaction will get any space to proceed their transactions. There are many reasons you can get space issues. Some of them are:
- Data or Log drive is full.
- Database file/s has restricted file growth
- Database File Autogrowth is not enabled.
- Some tables are growing faster than our prediction.
- Some long running transaction is eating all space.
We generally get error 1105 when any database including tempdb or any transaction log file get full and runs out of space. File growth restriction is also a possible reason you can get error 1105. Make sure to check all database files associated with your database to make sure there is no file growth restriction is set that is causing to generate this error. Sometimes when you restrict the file growth and the file size reached its limit, you may get this error message when trying insert / updates on database. I have explained multiple solutions to overcome this issue. Have a look at below section to get the solution of such space issues.
Solution
To fix this error we need to make space in the database so that all running transactions can execute and log their operations. We have multiple solutions to fix such issue based on the nature of the issues. Some of the solutions are given below:
- First you should check if any unwanted file is not there in data or log drive. Sometimes we placed backup files in data or log drive and forgot to delete it. Make sure to remove or move such unwanted files to make space for your database.
- If any of the database files have restricted file growth then you should immediately turn on autogrow feature of the data or log file. You can read below article to understand what should be the perfect value for autogrowth of a data or log file.
How to Enable Autogrowth and What should be the Best value for File Autogrowth?
- If size of your database file is limited to some value then make sure to extend the size of that file or I would suggest going with unrestricted growth. Read above attached article to get more about this.
- If your data or log drive is running out of space then you should add more files to the file group on different drive where you have enough space. You can use below command to add any data or log file to your desired drive. Make sure to change the drive path as per your correct path.
ALTER DATABASE Techyaz ADD FILE ( NAME = techyaz_Data3, FILENAME = 'F:\MSSQL\DATA\techyaz_DataFile3.ndf')
- We can also free disk space by dropping index or tables that are no longer needed. You can also consider data purging for unwanted tables or indexes.
- You can also consider moving some of the database files to different drive to make some space for your database.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Read More:
- Fix Error 1101: Could not allocate a new page for database
- Why should we Avoid Database Shrink Operation?
- Why should we always Disable Autoshrink property?
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
