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

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

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 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.

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 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.

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

SYMPTOMS

CAUSE

RESOLUTION

WORKAROUND

STATUS

REFERENCES