Microsoft KB Archive/934378

= Space is not released after you delete some rows from a table in SQL Server 2000 =

Article ID: 934378

Article Last Modified on 4/4/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition

-



SYMPTOMS
Consider the following scenario. You delete some rows from a table in Microsoft SQL Server 2000. You run the sp_spaceused stored procedure against the table. In this scenario, the result of the sp_spaceused stored procedure shows that lots of space in the table is not released. This problem is not resolved even after you run the DBCC UPDATEUSAGE statement against the table.



CAUSE
This problem occurs because the Page Free Space (PFS) ghost flag is not set correctly on the PFS page. Therefore, the ghost row clean-up thread thinks that no ghost row exists in the data pages that are covered by the PFS page.



WORKAROUND
To work around this problem, use one of the following methods:
 * Rebuild the index or the heap that the data that you deleted belongs to. After the index or the heap is rebuilt, the PFS page will be re-created. Additionally, the PFS ghost flag will be set correctly on the PFS page.
 * Add a new record to the table, and then delete the row that you added.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
When a row is deleted from a table, the following things occur:
 * The row is marked to be a ghost row.
 * The PFS page is updated. It indicates that the data page where the deleted row originally existed contains a ghost row.
 * A PFS ghost flag is set on the PFS page.

When the ghost row clean-up thread runs, the thread performs the following steps:
 * 1) The thread checks whether the PFS ghost flag is set on the PFS page. If the PFS ghost flag is not set, the thread knows that no ghost row exists in the data pages that are covered by the PFS page. The thread will skip the PFS page and check the next PFS page.
 * 2) If the PFS ghost flag is set on the PFS page, the thread searches in the PFS page to find data pages that have ghost rows.
 * 3) For each data page that the thread finds, the thread deletes the ghost rows in the data page.

Keywords: kbexpertiseadvanced kbtshoot kbprb KB934378

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.