Export (0) Print
Expand All

Troubleshooting Insufficient Data Disk Space

During recovery, the SQL Server Database Engine might require additional disk space for data files. When an operation lacks sufficient disk space, the Database Engine issues 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 while the database is online, the database remains online, but data cannot be inserted. If the disk fills during recovery, the Database Engine marks the database as "resource pending." In either case, user action is required to make disk space available.

One of the following actions might make space available to the filegroup:

  • Free disk space on the full disk.

  • Move data files to another disk.

  • Add files on a different disk.

  • Enable autogrow.

NoteNote

If the error occurred during database recovery, you must recover the database after resolving the problem.

To free disk space on the full disk

  • On the disk containing a file in the filegroup mentioned in the error message, free disk space by dropping any unnecessary indexes or tables. Freeing disk space allows the files in the filegroup to grow.

To move data files to another disk

To add files on a different disk (Transact-SQL)

  • Add more files to the filegroup on a different disk by using ALTER DATABASE <database_name> ADD FILE TO FILEGROUP <filegroup_name>.

To add files on a different disk (SQL Server Management Studio)

To increase the file size

If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:

  • Manually increase the file size to produce a single growth increment.

  • Enable autogrow by using the ALTER DATABASE statement to set a nonzero growth increment for the FILEGROWTH option.

NoteNote

In either case, if the current size limit has been reached, increase the MAXSIZE value.

To recover the database

If the database was in recovery when it ran out of disk space, recover the database by using ALTER DATABASE <database_name> SET ONLINE.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft