DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns| Article ID | : | 324432 | | Last Review | : | December 9, 2005 | | Revision | : | 5.1 |
This article was previously published under Q324432 SYMPTOMS 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.
 Back to the top
RESOLUTION 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 (http://support.microsoft.com/kb/257425/EN-US/) 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 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.groupname
from 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.groupid
group by sfg.groupname
| | 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]
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. |
Back to the top MORE INFORMATION 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 pubs
go
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8
begin
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)
)
end
go
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7
begin
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
)
end
go
create procedure import_extentinfo as
dbcc extentinfo('<database name>')
go
insert extentinfo exec import_extentinfo
go
select [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. Back to the top REFERENCESFor additional information, click the
article numbers below to view the articles in the Microsoft Knowledge Base: 272220 (http://support.microsoft.com/kb/272220/EN-US/) FIX: TEXT/IMAGE Data Storage Space Not Reclaimed Correctly
308627 (http://support.microsoft.com/kb/308627/EN-US/) FIX: DBCC SHRINKDATABASE or DBCC SHRINKFILE May Expand Database with Text or Image Data
Back to the top
APPLIES TO| • | Microsoft SQL Server 2000 Standard Edition | | • | Microsoft SQL Server 7.0 Standard Edition |
Back to the top | Other Support Options- Need More Help?
Contact a Support professional by Email, Online or Phone. - Customer Service
For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more. - Newsgroups
Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
|
|
Help and Support
