How to quickly shrink/remove a database file

How to quickly shrink/remove a database file

Quickly is a relative term and this could mean turning a 2 week task into a 2 day task.

Normally what you would do if you wanted to remove a datafile is clear it of data and the remove it. You would use the DBCC SHRINKFILE (LogicalFileName, EMPTYFILE) command to do this.

You might also instead of shrinking a file, delete it and then recreate it at the desired size

Depending on the size of the file this could take days, maybe even weeks. I have witnessed the empty file moving data at a rate of 1MB a minute so if your file is several TB it will take a very long time.

However there are things we can do to speed it up

First of all put your file into read only mode you can do this using DBCC SHRINKFILE (LogicalFileName, EMPTYFILE). From books online “Emptyfile assures you that no new data will be added to the file.” which is exactly what we want

We now want to move all the tables and indexes off the file and onto other files in the filegroup we do this by rebuilding all the heaps and indexes. Use the following code to generate scripts

select concat(‘exec (”alter table ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,b.index_id
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where a.type_desc = ‘Heap’ and data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
UNION ALL
select DISTINCT concat(‘exec(”alter INDEX ALL ON ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,1
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
ORDER BY 2 DESC
Before running this code you will need to disable any columnstore indexes on your tables
select concat(‘ alter index ‘, a.name , ‘ on ‘,c.name,’.’,b.name , ‘ disable’) from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
inner join sys.schemas c on b.schema_id = c.schema_id
where a.type > 5

When this has completed you can check the amount of space left using the following code

IF (SELECT OBJECT_ID(‘tempdb..#a’)) IS NOT NULL DROP TABLE #a
IF (SELECT OBJECT_ID(‘tempdb..#b’)) IS NOT NULL DROP TABLE #b
IF (SELECT OBJECT_ID(‘tempdb..#c’)) IS NOT NULL DROP TABLE #c
SELECT DB_NAME(a.database_id) db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE ‘ + QUOTENAME(DB_NAME(a.database_id))
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + DB_NAME(a.database_id) + ”’,”’
+ a.name + ”” get_file_size
INTO #a
FROM sys.master_files a
INNER JOIN sys.databases b ON b.database_id = a.database_id
WHERE b.state_desc = ‘ONLINE’ AND b.name !=’tempdb’ –master files will not give accurate data for temp log;
AND b.user_access_desc = ‘MULTI_USER’
UNION ALL
SELECT ‘tempdb’ db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE [tempdb]’
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + ‘tempdb”,”’
+ a.name + ”” get_file_size
FROM tempdb.sys.database_files a
SELECT get_file_size INTO #b FROM #a

WHILE EXISTS (SELECT 1 FROM #b)
BEGIN
DECLARE @sql VARCHAR(MAX) = (SELECT TOP 1 get_file_size FROM #b ORDER BY 1)
DECLARE @a TABLE (size DEC(10,2),name_db VARCHAR(100),name_file VARCHAR(100))

INSERT INTO @a
EXEC(@Sql)

DELETE FROM #b WHERE get_file_size = @sql
END

SELECT name_db,name_file,a.size,a.size-b.size AS free_space,CAST(100*(a.size-b.size)/a.size AS DEC(10,2)) free_space_percentage,a.growth,a.is_percent_growth,GETDATE() collection_time
INTO #c
FROM #a a
INNER JOIN @a b ON a.name = b.name_file AND a.db_name = b.name_db
SELECT * FROM #c

If there is still a significant amount of space being used in the file you can check what it by using the following

select top 1 * from sys.dm_db_database_page_allocations(7,NULL,NULL,NULL,’limited’) where allocated_page_file_id = 8 and object_id > 100
Where 7 is the database id, I use top 1 because I find the code can take more than 24 to return a complete data set

you can then rebuild the heap and tables to shift the data off the file

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: