DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns

This article was previously published under Q324432
This article has been archived. It is offered "as is" and will no longer be updated.
The DBCC SHRINKFILE and DBCC SHRINKDATABASE commands will not move data and reduce the file size if the EstimatedPages value equals the MinimumSize value that is reported when the command completes. Because of data modification queries on tables with binary large object (BLOB) data types (text, ntext, image), it is possible to have many uniform extents allocated that are sparsely populated with data (for example, only 1 of the 8 pages that the extent controls is allocated), which will cause the DBCC SHRINKFILE and the DBCC SHRINKDATABASE commands to over-estimate the number of EstimatedPages, and the commands do not perform the shrink operations. Typically, in cases where this behavior is experienced, the size of a full database backup is up to 8 times smaller than the sizes of the files of the database after you run a DBCC SHRINKFILE on the database files.
If the affected tables do not have to be continuously online while you correct this situation, you can use any one of the following workarounds to resolve this problem:
  • Use a SELECT INTO statement to transfer the whole table to a new table in the same database. Drop the original table, and then run a DBCC SHRINKFILE statement. Rename the new table to the original table name.
  • Use a SELECT INTO statement to transfer the whole table to a new table in a different database. Truncate the original table, and then run a DBCC SHRINKFILE statement. 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. Run a DBCC SHRINKFILE statement. Create a new table, and then use the bulk copy program to copy the data back to the table.
  • Use Data Transformation Services (DTS) to transfer the whole database to a new database. SQL Server 7.0 Data Transformation Services may not transfer text and image data longer that is 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
    You can look for text and image columns that are 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.
If the database must be online or the size of the database is too large to move the data into an external file or another database, you can use the DBCC SHRINKFILE command with the EMPTYFILE option. To use this work around, follow these steps:
  1. If you are running Microsoft SQL Server 7.0 Service Pack 3, turn on trace flags 1180 and 1197.
  2. If you running Microsoft SQL Server 7.0 Service Pack 3 build 7.00.987, or later, including Service Pack 4, turn on trace flag 1180.
  3. If you have spread the database across several files for either drive space issues or performance reasons, create 1 or more additional files in the database by using the ALTER DATABASE command with a total size comparable to the results of the following query.
    select 'size in MB'=ceiling(sum(ei.pg_alloc)/128.000), sfg.groupnamefrom extentinfo ei inner join sysfiles sf on ei.[file_id] = sf.fileid and ei.[file_id] != 1 inner join sysfilegroups sfg on sf.groupid = sfg.groupidgroup by sfg.groupname
    NOTE: The preceding query uses the table that is created by the query in the "More Information" section of this article. Additionally, the file size totals are broken down by filegroup.

  4. Run a DBCC SHRINKFILE query, with the EMPTYFILE option as the second parameter, on each file that has sparsely populated blob uniform extents.
  5. You can track the progress of the DBCC SRHINKFILE command by creating one or more tables with the same schema as the extentinfo table from the "More Information" section of this article. Then run an "INSERT .. EXEC import_extentinfo" query to populate the data to the new tables. Use the following query to analyze the results:
    select [file_id], 'page count'=sum(pg_alloc), 'min page'=min(page_id), 'max page'=max(page_id) from extentinfo group by [file_id]
    Additionally, you can use the following query to keep track of when the DBCC EXTENTINFO command was run if the tables start with the same pattern of letters and are created near the time as the DBCC EXTENTINFO command:
    select crdate, [name] from sysobjects where type = 'U' and [name] like 'extent%' order by crdate
  6. When the DBCC SHRINKFILE query completes, run an ALTER DATABASE query to remove the old files from the database.
  7. You can possibly remove additional space by running a DBCC SHRINKFILE query with the TRUNCATEONLY option on the new files that were created in step 3.
  8. If you are running Microsoft SQL Server 7.0, turn off the trace flags you turned on in step 1 or 2.
You can run the following Transact-SQL query from any query tool, such as Query Analyzer, if you are logged in as a user who has sysadmin rights. You run the Transact-SQL query on SQL Server to determine if the DBCC SHRINKDATABASE command and the DBCC SHRINKFILE command cannot shrink files because of sparsely populated BLOB uniform extents.

NOTE: You must update the string '<database name>' to the name of the problem database, and enclose the name in single quotation marks.

use pubsgoif convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8begin    create table extentinfo    (   [file_id] smallint,   page_id int,   pg_alloc int,   ext_size tinyint,   obj_id int,   index_id tinyint,   pfs_bytes varbinary(10)   )endgoif convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7begin   create table extentinfo    (   [file_id] smallint,   page_id int,   pg_alloc int,   ext_size tinyint,   obj_id int,   index_id tinyint,   pfs_bytes varbinary(10),   avg_used tinyint   )endgocreate procedure import_extentinfo asdbcc extentinfo('<database name>')goinsert extentinfo exec import_extentinfogoselect [file_id],obj_id, index_id, ext_size, 'actual extent count'=count(*), 'actual page count'=sum(pg_alloc), 'possible extent count'=ceiling(sum(pg_alloc)*1.0/ext_size), 'possible extents / actual extents' = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*) from extentinfo where ext_size != 1 and index_id = 255 group by [file_id],obj_id, index_id, ext_size having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0 order by obj_id, index_id, [file_id]
If you see result rows from the last query that have index_id values of 255, and the possible extent / actual extent column is less than 75, this database does have sparsely populated BLOB extents and can be reduced in size by the methods described in the "Resolution" section of this article.

NOTE: This query relies on the functionality of the DBCC EXTENTINFO command. Do not use the DBCC EXTENTINFO command outside this situation because the functionality may change or even be removed from future versions of Microsoft SQL Server.
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
272220 FIX: TEXT/IMAGE Data Storage Space Not Reclaimed Correctly
308627 FIX: DBCC SHRINKDATABASE or DBCC SHRINKFILE May Expand Database with Text or Image Data

Article ID: 324432 - Last Review: 12/07/2015 11:28:27 - Revision: 5.1

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbprb KB324432