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
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
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.
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.
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.
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.
We can still see there is no fragmentation.
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.
We can see the index is now fragmented.
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
