You are currently offline, waiting for your internet to reconnect

Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Bug #: 219514 (SQL Server 8.0)
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 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.
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 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.
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 is not 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 the "Creating a Clustered Index" topic in SQL Server Books Online.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 913399 - Last Review: 11/02/2007 09:50:43 - Revision: 1.2

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
Feedback
4050&did=1&t=">='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> amp;did=1&t=">