As a SQL Server database administrator, you may have encountered various errors related to disk space running out. In this blog post, we will discuss some of the common error messages you might come across in the SQL Server error logs when the database engine is unable to obtain the necessary disk space from the underlying disk subsystem.

One common scenario where these errors are seen is during the recovery operation when SQL Server is restarted. During recovery, the SQL Server Database Engine may require additional disk space for data files. If there is insufficient disk space, the Database Engine will issue either an 1101 or 1105 error, depending on whether it is unable to allocate space for an extent or an object, respectively.

If the disk fills up while the database is online, the database will remain online, but data insertion will be halted. On the other hand, if the disk fills up during recovery, the Database Engine will mark the database as “resource pending.” In both cases, user action is required to free up disk space.

Here are some of the typical errors you may encounter:

• Error 1101: Could not allocate a new page for database ‘database_name’ because of insufficient disk space in filegroup ‘filegroup_name’. To resolve this issue, you can create space by dropping objects in the filegroup, adding additional files to the filegroup, or enabling autogrowth on existing files in the filegroup.
• Error 1105: Could not allocate space for object ‘object_name’ in database ‘database_name’ because the ‘filegroup_name’ filegroup is full. To resolve this issue, you can create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or enabling autogrowth on existing files in the filegroup.
• Error 9002: The transaction log for database ‘database_name’ is full. To find out why space in the log cannot be reused, you can check the log_reuse_wait_desc column in the sys.databases system view. To resolve this issue, you can free up disk space on the full disk, move data files to another disk, add files on a different disk, enable autogrow, or check if the fixed size has been set. If you are using SQL Express Edition, make sure it does not exceed the database size limits.

If you have been working with SQL Server for some time, you have likely encountered some of these errors. If you believe I missed any scenarios from the above list, please let me know.