FIX: DBCC ShrinkDatabase or ShrinkFile Commands May Cause Errors 5042 and 3140 (254253)

SYMPTOMS

If you use the DBCC ShrinkDatabase or ShrinkFile commands, the following errors may occur:

Error 5042 - The file '%' cannot be removed because it is not empty.

-and-

Error 3140 - Could not adjust the space allocation for file '%'.

CAUSE

The shrink code line in the SQL Server 7.0 product does not account for empty text or image extents created when multiple deletions take place on the same extent simultaneously.

The Page Free Space (PFS) entries are scanned when SQL attempts to locate the target page to "shrink to". However, under these conditions the Index Allocation Map (IAM), Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) bits are correctly set to show the extent is still in use. However, the PFS bits correctly show no pages on the extent that remains in use (allocated).

The first pass of the shrink is responsible for deallocating all extents that no longer have pages in use. This scan does not account for text or image indexes resulting in a condition where an extent is allocated in the database at a higher location than the last allocated page shown in the PFS scan. When the shrink attempts to remove the extent in the file, the errors occur because the text or image extent is still valid.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

SQL Server can avoid immediate deallocation of an extent when multiple delete operations are simultaneously taking place on the extent. These extents may be:

Reused by future allocations for the object.
Removed by shrink operations.
Removed by a ghost cleanup.

Because this is a valid condition, the DBCC checkdb/newalloc output does not report an error.

In some cases, you can apply workaround operations. However, if the StatBlob column in the sysindexes system table encounters this condition, it may be difficult to use a workaround.

The correction is entirely new code in the 7.0 code line, which can only be enabled using a trace flag. Enabling -T8901 instructs the shrink to remove empty text or image extents.