Monday, November 7, 2011

0 Shrink the database File: DBCC SHRINKFILE

Recently I come across the need to shrink the database file to avoid the file system being filling up.

The first thoughts come to the mind is using DBCC SHRINKFILE

DBCC SHRINKFILE with TRUNCATEONLY option only remove the free pages after the last allocate extend. It does not perform any page moving movement. Hence it is generally no harm and perform very quick.

If that is not enough, we can use DBCC SHRINKFILE(FILENAME,TARGETSIZE).

  1. DBCC SHRINKFILE (AdventureWorks2008R2_Data,300)

This will perform the page movement and it will be slower and take much longer time. This may possible shrink the file smaller then its initialize size as long as there is free space within the file.

To check the free space within the file, we can run

  1. SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,size/128.0
  2. FROM sys.database_files;
  3. go


This information is different from what you get from sp_spaceused. sp_spaceused is given you the total size combine with log and data. the sys.database file is break down by each database file.


The DBCC SHRINKFILE process can be stop at any time. Any work has been complete will retain.

In many case, you may wonder how long does it take to complete,we can query from sys.dm_exec_requests

  1. SELECT session_id,start_time,status,command,DB_NAME(Database_id),wait_type
  2. ,percent_complete,DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,
  3. (estimated_completion_time/1000/60) AS EstimatedMinutesToEnd
  4. FROM sys.dm_exec_requests

DBCC Shrinkfile does not retain the index fragmentation and could possible make it worse. It is suggest to check the index fragmentation after you shrink the file and decide whether it is need to rebuild or recognize.

Below query can be used to check the index fragmentation on the given database and given file group.

  1. SELECT i.data_space_id,OBJECT_NAME(dmi.object_id) AS TableName,dmi.index_id, AS IndexName,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent
  2. FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL , NULL) dmi
  3. JOIN  sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id


Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates