Monday, November 7, 2011

1 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


  1. OMEGA buy replica watches has been a world leader in advanced watch design replica omega since 1848. Over the years, the brand has been widely celebrated for their durability and precision. OMEGA has served as the official timekeepers of the Olympic Games for nearly a century. They also created the official watch of the space program and first watch to land on the moon. Buying an OMEGA watch is an excellent way to invest in a piece of history.



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