- A deletion on this table occurs.
- A table-level lock is being held.
If pages are not deallocated, other objects in the database cannot reuse the pages.
However, when you enable a row versioning-based isolation level in a SQL Server 2005 database, pages cannot be released even if a table-level lock is being held. For more information about row versioning-based isolation levels, see the "Using Row Versioning-based Isolation Levels" topic in SQL Server 2005 Books Online.
- Include a TABLOCK hint in the DELETE statement if a row versioning-based isolation level is not enabled. For example, use a statement that is similar to the following:Note <TableName> represents the name of the table.
DELETE FROM <TableName> WITH (TABLOCK)
- 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 the "Creating a Clustered Index" topic in SQL Server Books Online.
Article ID: 913399 - Last Review: Mar 29, 2017 - Revision: 3