大网数据广告活动
数据库与应用安全

Space used by a table isn't released

· 52CE 我爱测

This article provides workaround for the problem that a table uses can't be released after you use a DELETE statement to delete all data from the table.

This article helps you work around the problem that a table uses can't be released after you use a DELETE statement to delete all data from the table.

Original product version:   SQL Server
Original KB number:   913399

Symptoms

After you use a DELETE statement in Microsoft SQL Server to delete data from a table, you may notice that the space that the table uses isn't completely released. When you then try to insert data in the database, you may receive the following error message:

Could not allocate space for object 'TableName' in database 'DatabaseName' because the 'PRIMARY' filegroup is full.

Note: TableName represents the name of the table. DatabaseName represents the name of the database that contains the table.

Cause

This problem occurs because SQL Server only releases all the pages that a heap table uses when the following conditions are true:

  • A deletion on this table occurs.
  • A table-level lock is being held.

Note: A heap table is any table that is not associated with a clustered index.

If pages aren't deallocated, other objects in the database can't reuse the pages.

However, when you enable a row versioning-based isolation level in a SQL Server database, pages can't be released even if a table-level lock is being held. For more information about row versioning-based isolation levels, see Isolation levels in the SQL Server Database Engine.

Workaround

To work around this problem, use one of the following methods:

  • Include a TABLOCK hint in the DELETE statement if a row versioning-based isolation level isn't enabled. For example, use a statement that is similar to the following:

    DELETE FROM <TableName> WITH (TABLOCK)
    

    [!NOTE] <TableName> represents the name of the table.

  • Use the TRUNCATE TABLE statement if you want to delete all the records in the table. For example, use a statement that is similar to the following:

    TRUNCATE TABLE <TableName>
    
  • Create a clustered index on a column of the table. For more information about how to create a clustered index on a table, see Create Clustered Indexes.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 11/04/2022
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。