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

Article translations Article translations
Article ID: 308627 - View products that this article applies to.
This article was previously published under Q308627
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 101422 (SQLBUG_70)
Expand all | Collapse all

On This Page

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

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:
  1. 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.
  2. 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.
  3. 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.

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.

REFERENCES

SQL Server Books Online; topic: "DBCC SHRINKFILE"

Properties

Article ID: 308627 - Last Review: February 27, 2014 - Revision: 4.1
APPLIES TO
  • Microsoft SQL Server 7.0 Service Pack 2
  • Microsoft SQL Server 7.0 Service Pack 3
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbsqlserv700presp4fix KB308627

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com