Dela via

  • Artikel

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.

Resolving the Space Problem

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.

Note

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>.

Note

For more information, see Adding and Deleting Data and Transaction Log Files.

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.

Note

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.