Archive for October, 2018

Shrinking database files

October 22, 2018

Prevent New Data from Being Written to a File

The database file will not shrink in a timely fasion while there is data in the file. So remove it. On a daily basis tables our tables are dropped and recreated. The fastest way to remove data is to let automatic process drop tables and not allow the processes to recreate the tables on the file we are trying to shrink.We can make a file readonly by using DBCC SHRINKFILE (‘filenname’,EMPTYFILE), as long as the command is running it will 1. not allow new data to be written to the file 2. slowly move data out of the file. The command frequently fails with a deadlock allowing data to be written to the file so by using the following work in procedure we can tell the proc to restart on failure.

CREATE PROC [dbo].[emptyfile_new]
@dbase VARCHAR(100) ,
@log_file_name VARCHAR(1000)


DECLARE @sql VARCHAR(MAX) = 'USE ' + @dbase + '; DBCC SHRINKFILE (N''' + @log_file_name + ''' , EMPTYFILE)';

EXEC (@sql);



EXEC test.dbo.emptyfile_new;


Leave the procedure running for the file you want to empty, if you want to empty multiple file, run the procedure multiple times in different sessions using different parameters.

Columnstore Indexes

You can not use shrinkfile on a file that contains a table with a columnstore index on it, move any table to a new temporary filegroup. Make sure to transfer any compression setting, indexes, keys, constraints.

You can not use shrinkfile on a file that contains a columnstore index. Move any columnstore indexes to a new tempory filegroup.

How much Data is in your file

SELECT FILEPROPERTY(‘filename’,’SpaceUsed’)

This will return the number of pages in your file. Multiply by 8 to get the number of kb.

What data is contained in my file

We can see how many pages of data each table/index has in the database using the following code:


select object_id,index_id,allocation_unit_type_desc,COUNT(*) c
into #a
from sys.dm_db_database_page_allocations(DATABASE_ID,NULL,NULL,NULL,'limited') where allocated_page_file_id = DATABASE_FILE_NUMBER
group by object_id ,index_id,allocation_unit_type_desc

,CASE WHEN a.index_id IN (0,1) THEN 'alter table '+ + '.' + ' rebuild'
WHEN a.index_id > 1 THEN 'ALTER INDEX ' + + ' on '+ + '.' + + ' rebuild'
INNER JOIN sys.objects b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.indexes d ON d.index_id = a.index_id AND d.object_id = a.object_id

Move Remaining Data

heaps and clustered indexes can be moved using ALTER TABLE X.X REBUILD

Other indexes can be moved using ALTER INDEX x ON x.x REBUILD

If these tables or indexes contain lob data then rebuilding will not move this instead copy data to a holding table, truncate the original table and move it back to the original table, while the emptyfile command is running.

You can identity Lob data on the file using the above SQL allocation_unit_type_desc field.

Check Page Contents

Occasionally you might get an error that where you are told a page cannot be moved, use the following to check the page contents


Switch the final parameter for a 3 to actual data on the page

You may be told that a file can not be shrunk as it is being shrunk by another process – even though it is not. In this case increase the file size by a small amount such as 10MB and retry your shrink.