Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 913399 - Last Review: November 2, 2007 - Revision: 1.2
Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server
Bug #: 219514 (SQL Server 8.0)
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 is not 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.
represents the name
of the table.
DatabaseName represents the
name of the database that contains the table.
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 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.
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 is not enabled. For example, use a statement
that is similar to the following:
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.
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
in the "Applies to" section.
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Desktop Engine (Windows)
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Personal Edition
- Microsoft SQL Server 7.0 Standard Edition
| kbexpertiseadvanced kbprb KB913399 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate