- 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 64You can look for text and image columns that are greater than 64 KB with the following query: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.
SELECT MAX (DATALENGTH (TextImageColumn)) FROM TableName
- If you are running Microsoft SQL Server 7.0 Service Pack 3, turn on trace flags 1180 and 1197.
- 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.
- 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.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.
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
- Run a DBCC SHRINKFILE query, with the EMPTYFILE option as the second parameter, on each file that has sparsely populated blob uniform extents.
- 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: 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 [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
- When the DBCC SHRINKFILE query completes, run an ALTER DATABASE query to remove the old files from the database.
- 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.
- If you are running Microsoft SQL Server 7.0, turn off the trace flags you turned on in step 1 or 2.
NOTE: You must update the string '<database name>' to the name of the problem database, and enclose the name in single quotation marks.
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8
create table extentinfo
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7
create table extentinfo
create procedure import_extentinfo as
dbcc extentinfo('<database name>')
insert extentinfo exec import_extentinfo
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]
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.
Article ID: 324432 - Last Review: Jul 10, 2008 - Revision: 1