One of my clients contacted me for assistance with whom I worked earlier to perform a Comprehensive Database Performance Health Check. They were running a job in SQL Agent and it was failing with error – Could not allocate space for object ‘EmployeeTransactions’ in database ‘PAMOSDB’ because the ‘PRIMARY’ filegroup is full and The File Size, Max Size Cannot be Greater Than 2147483647 in Units of a Page Size.
Here is the first article which I shared with them. SQL SERVER – Msg 1105 – Could Not Allocate Space for Object Name in Database ‘DB’ Because the ‘PRIMARY’ Filegroup is Full
They came back with the answers which I had on my blog.
- Check if auto-growth is restricted. – No
- Check if auto-growth is set to a high value which would cause disk space to become full. – No
- Check if disk space is low. – No
- Check SQL edition because SQL Express has a database file size limit. – No
And now I was thinking, how is that possible? I went on a call with them and started desktop sharing to see what exactly is happening.
I checked the auto-grow size and it was set to 1 GB. I looked at the value and asked them to run ALTER command with 1 GB increment to the current size. It was something like below.
USE [master] GO ALTER DATABASE [PAMOSDB] MODIFY FILE (NAME = N'PAMOSDB', SIZE = 16779237 MB) GO
When I executed the command manually, it raised below error:
Msg 1842, Level 16, State 1, Line 3
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.
The error message talks about page size, which is 8 KB in size. Wait a second. Let’s do a little math here.
- 2147483647 Pages
- 2147483647 * 8 KB
- 17179869176 KB
- 99 MB
- 99 GB
- 16 TB
The number which I have in the query is more than 16 TB (actually, its 16.00192 TB). I checked the documentation of SQL Server capacity specification and found that a single data file in the database can’t be more than 16 TB in size. Here is the link and the snippet taken from there. Maximum capacity specifications for SQL Server
Now, this all makes sense. In my original blog, I should add this question also.
- Are you increasing data file more than 16 TB or log file more than 2 TB?
WORKAROUND/SOLUTION
SQL Server has a hard limit of 16 TB per data file. If we need more space in the filegroup then we need to add another data file in the same filegroup.
If we are hitting space issue with the log file, then we need to free up space from LDF file SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server
If it’s not possible to get free space, then add another log file.
I am sure not many might hit this scenario as I have heard about this first time in many years. If you have seen this error, please comment and let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
