Execute SQL Server DBCC SHRINKFILE Without Causing Index Fragmentation

By:   |   Comments (5)   |   Related: > Database Console Commands DBCCs


Problem

I have a SQL Server database data file and by accident I expanded the data file way too big. The database was reindexed the day before and I have heard that shrinking databases causes fragmentation.  Does the database data file shrink operation using DBCC SHRINKFILE always cause fragmentation?

Solution

In the situation where there is known free space at the end of database data file, the DBCC SHRINKFILE with TRUNCATEONLY option allows the SQL Server database data file to be shrunk without performing any page movement inside the file and hence not causing fragmentation. A thing to note is that the minimum database data file size can only be shrunk to the last allocated extent.

For example, the DBA can use this method for a database data file that was accidently set to an unintended large size. The DBCC SHRINKFILE with TRUNCATEONLY can be executed with an appropriate target size. If the target size specified is 0, this will shrink the database data file to the last allocated extent. The DBA should then resize the database data file to an appropriate size.

The command to perform the SQL Server database data file shrink operation without page movement is below:

USE <<database_name>>
GO
DBCC SHRINKFILE (N'<<database_filename>>', <<target_size>>, TRUNCATEONLY)
GO

SQL Server DBCC SHRINKFILE Demonstration

Below are contrived examples to simulate three scenarios:

  • DBCC SHRINKFILE with TRUNCATEONLY to a target size which does not cause index fragmentation
  • DBCC SHRINKFILE with TRUNCATEONLY to the last allocated extent which does not cause index fragmentation
  • DBCC SHRINKFILE which causes fragmentation

All the T-SQL in this tip was written and executed in SQL Server 2014 Developer Edition.

Below are the steps to prepare a test database with two tables:

Step 1

A blank database with an initial database data file size of 5GB is created.

For the purpose of measuring index fragmentation before and after the database data file shrink operation, two tables with very minimal index fragmentation are created and populated to occupy the 5GB of database data file space. We will create a clustered index on the primary key column.

For each of the tables to occupy 2.5GB of data space in our example, each table will require approximately 300,000 8KB data pages. Since each row in the table will occupy one data page, we will be inserting 305,000 rows into each table concurrently and then the top 5000 rows will be deleted to make sure we have empty data pages at the beginning of the data file. The query below took around 3 minutes to complete on my laptop. The record insertion will not create any index fragmentation because the identity values generated are in a sequential order

USE master
GO
CREATE DATABASE [TestFileShrink]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'TestFileShrink_data', FILENAME = N'D:\DATA\TestFileShrink_data.mdf', SIZE = 5GB, FILEGROWTH = 1GB)
LOG ON 
( NAME = N'TestFileShrink_log', FILENAME = N'E:\Log\TestFileShrink_log.ldf', SIZE = 4GB, FILEGROWTH = 1GB)
GO

USE [TestFileShrink]
GO
CREATE TABLE dbo.Table1 (col1 INT IDENTITY(1,1) PRIMARY KEY, col2 char(7000) default 'Any value in Table 1');
GO
CREATE TABLE dbo.Table2 (col1 INT IDENTITY(1,1) PRIMARY KEY, col2 char(7000) default 'Any value in Table 2');
GO

SET NOCOUNT ON
GO
DECLARE @i INT = 1

BEGIN TRAN
WHILE @i <= 300000
   BEGIN
      INSERT INTO dbo.Table1 DEFAULT VALUES
      INSERT INTO dbo.Table2 DEFAULT VALUES
      SET @i += 1
   END
COMMIT TRAN

DELETE TOP (5000) FROM dbo.Table1
DELETE TOP (5000) FROM dbo.Table2

Step 2

Both table's index fragmentation are captured to be compared later:

USE [TestFileShrink]
GO
SELECT 
OBJECT_NAME(object_id) TableName
, index_type_desc, index_level
, avg_fragmentation_in_percent
, page_count
, record_count
FROM [sys].[dm_db_index_physical_stats](DB_ID('TestFileShrink'), NULL, NULL, NULL, 'DETAILED')
GO

Initial SQL Server Database Fragmentation

Step 3

The database data file free space available on the data file is approximately 400MB after both tables are populated:

USE [TestFileShrink]
GO
SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files
WHERE type_desc = 'ROWS'
GO

Initial SQL Server Free Space

Scenario 1 - DBCC SHRINKFILE with TRUNCATEONLY to a target size which does not cause index fragmentation

As an example, a DBA who is supposed to be on sick leave was still working in the office with a bad migraine. The DBA connects to SQL Server to allocate more space to the TestFileShrink database.  The DBA allocates the space using SQL Server Management Studio (SSMS) and tries to allocate an additional 2GB to the database. There was a typo and the value entered was 70GB and the command took effect almost immediately because Instant File Initialization was enabled.

The DBA gets a monitoring alert that the server disk space hosting this particular database was close to full.

The DBA runs a query to check the free space which is now 65GB. The DBA realized the mistake of specifying the database data file to an unintended large size.

SQL Server Free Space after Database File Size Change

The size intended for this database was 7GB. The DBA performs a DBCC SHRINKFILE with TRUNCATEONLY to a target size of 7GB.

USE [TestFileShrink]
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 7000, TRUNCATEONLY)
GO

After shrinking the database data file size, the DBA runs a query to check the database data file space for the TestFileShrink database to confirm DBCC SHRINKFILE has taken effect.

SQL Server Database Current Size and Free Space

The DBA checks the database fragmentation and confirms DBCC SHRINKFILE did not cause any index fragmentation because there has been no data movement as part of the data file shrink process using TRUNCATEONLY.

Post SQL Server Database Fragmentation

Scenario 2 - DBCC SHRINKFILE with TRUNCATEONLY to the last allocated extent which does not cause index fragmentation

As a continuation of this learning exercise from Scenario 1, Table2 is truncated and we will shrink the database data file to its minimum size. Data pages of Table1 and Table2 interleave with each other. Hence, truncating Table2 will create empty data pages in between the data pages of Table1. But the last allocated extent for Table1 would still be close to 5GB.

USE [TestFileShrink]
GO
TRUNCATE TABLE dbo.Table2
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 0, TRUNCATEONLY)
GO

Below is the output of database data file size and index fragmentation. The database size is now a little over 5GB and Table1 does not show any index fragmentation, because there has been no page movement and the last allocated extent for Table1 is still close to 5GB, this explains why DBCC SHRINKFILE is unable to shrink below 5GB.

SQL Server Current Size and Free Space

We can still see there is no fragmentation.

SQL Server Database Fragmentation after DBCC SHRINKFILE

Scenario 3 - DBCC SHRINKFILE which causes fragmentation

A common mistake when reclaiming database data file space is using DBCC SHRINKFILE without specifying the TRUNCATEOLY argument. The T-SQL below will shrink the data file to 3GB. SQL Server will by default perform a NOTRUNCATE which will move data pages from the end of the file to any free space at the beginning of the database data file.

USE [TestFileShrink]
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 3000)
GO

Below is the output of database data file size and index fragmentation caused by the shrink process.

SQL Server Database Size after Shrink File

We can see the index is now fragmented.

Final SQL Server Database Fragmentation Showing Issues

Conclusion

The SQL Server DBCC SHRINKFILE command needs to be used with utmost care because if you use this command and it performs page movement to free up space, the operation is very slow, consumes CPU and I/O resources and in the process can heavily fragment indexes.

The database data file shrink with page movement will occur when the NOTRUNCATE option is specified or when this option is used by default.

Not all databases with large empty space in the data file need to be shrunk.  Sometimes database auto-growth is enabled and will kick-in due to scheduled operations such as index rebuilds. If you shrink a database data file with NOTRUNCATE which causes massive index fragmentation and then a SHRINKFILE operation followed by an index rebuild this will push the database back to the original size.

I am not an advocate of shrinking SQL Server database data files unless it is absolutely necessary.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Sunday, March 10, 2024 - 11:55:59 PM - Liliek Back To Top (92054)
Thanks for sharing, but I think by add new space to datafile it will add on last position of page. so when using truncate only, it will release from empty last page to os.

but truncate only can not used in case you do delete old record to archive.
you should use without truncate only.


Thursday, April 20, 2017 - 1:38:01 AM - rahul Back To Top (55050)

 

 Excellent article boss.. Expecting more the same kind of stuff..


Monday, August 1, 2016 - 10:58:58 AM - Ken Back To Top (43021)

When you're stuck for free space, shrinking back to the last allocated extent may not get you enough. I tried this in one of my test DBs; looking to reclaim 9.5 GB back but only got just over 1 GB. I wind up having to reclaim disk space at the expense of fragmentation :(

 Ken


Saturday, July 23, 2016 - 8:02:19 AM - Simon Liew Back To Top (42957)

Hi Mahmoud,
The database data file size can only be shrunk to the last allocated extent with this option.

Depending on how much the 33% equates to, you might not want to shrink the data file for reasons outlined in the conclusion section.


Friday, July 22, 2016 - 4:38:20 AM - mahmoud Back To Top (41947)

currently i have 31% free space in my data file , i tried to shrink the file using TRUNCATEONLY  but the size still the same and it doesn't free the spaces

please advise















get free sql tips
agree to terms