Pentana Audit: Error "Could not allocate space for object '[object name]' in database '[database name]' because the 'PRIMARY' filegroup is full."
Scenario
The user receives an error like this:
Universal Time: (2024-09-18 10:50:57) Local Time: (2024-09-18 11:50:57) TimeZone: (01:00:00 DST)
Version: 6.0.1.8
Could not allocate space for object '[object name]' in database '[database 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.
The same error will appear in the main service log.
Cause
When the database data file becomes full, SQL Server will attempt to grow it to add space. This error indicates that SQL Server could not extend the file. Potential causes are:
- The amount of space specified in the 'Autogrowth' setting is more than is available on the disk
- Autogrowth is not enabled, in which case the file is full and will not extend unless the DBA manually enlarges it
Solution
Ensure that the data file is able to grow and that there is sufficient space:
- Either manually extend the data file, or ensure that Autogrowth is enabled
- Ensure that there is enough space on the disk to enable the file to grow by the amount specified in the 'File Growth' setting
A special case: SQL Server Express
A database hosted in SQL Server Express is limited to 10 Gb, and when the size limit is hit this error will be shown.
In that case your options are to:
- Purge some data from the database, so that space inside the file can be reused. This is likely to be a temporary workaround, as more data will be added with normal use
- Move the database to a paid edition of SQL Server
A discussion of these options is outwith the scope of this article.