FIX: DBCC SHRINKDATABASE or DBCC SHRINKFILE May Expand Database with Text or Image Data

View products that this article applies to.

This article was previously published under Q308627

BUG #: 101422 (SQLBUG_70)

SYMPTOMS

If you perform a DBCC SHRINKFILE or DBCC SHRINKDATABASE on a database that has a lot of text, ntext, or image data, the database may actually grow instead of shrink.

This problem does not occur on SQL Server 7.0 RTM or SQL Server 7.0 Service Pack 1 (SP1).

Back to the top

CAUSE

The shrink operation might cause mixed text pages to have only a small amount of bytes on each 8-K page. As data is re-allocated by the shrink, the database file grows instead of shrinking.

To verify you are experiencing this issue, run the following query from Query Analyzer in the database you are trying to shrink. See if there is a large difference between the "reserved" and the "used" column in the sysindexes system table for any one particular table that has a text, ntext or image type column:

SELECT * FROM sysindexes WHERE indid = 255 ORDER BY reserved

Back to the top

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 (http://support.microsoft.com/kb/301511/EN-US/ ) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix should have the following file attributes or later:

   Date       Time      Version    Size      File name
   -------------------------------------------------------

   4/5/2001   8:07 PM   7.00.987   4.82 MB   Sqlservr.exe

NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

Back to the top

WORKAROUND

To work around this behavior, use any of these methods:

Use DTS to transfer the whole database to a new database. SQL Server 7.0 Data Transformation Services (DTS) may not transfer text and image data longer than 64 KB correctly. This problem does not apply to the SQL Server 2000 version of Data Transformation Services. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
257425 (http://support.microsoft.com/kb/257425/EN-US/ ) FIX: DTS Object Transfer Does Not Transfer BLOB Data Greater Than 64 KB
You can look for text and image columns greater than 64 KB with the following query:
SELECT MAX (DATALENGTH (TextImageColumn)) FROM TableName
If the text and image data is greater than 64 KB, the transfer truncates the data and does not raise any error except in the DTS log files. If you have text or image data greater than 64 KB, use the information in the third bullet item for the tables that have text or image data. Next, use DTS for the rest of the items in the database.
Use SELECT INTO to transfer the whole table to a new table in a different database. Next, truncate the original table and perform a DBCC SHRINKFILE. Transfer the data back to the original table.
Use the bulk copy program to copy the table out in native mode. Script out the table, and then drop the existing table. Next, perform a DBCC SHRINKFILE. Create a new table, and then use the bulk copy program to copy the data back to the table.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

Back to the top