Why Sql server Database Data file shrink is not a good practice?

Often in DBA world you hear shrink data files and log files. Data file shrink is not a good approach and leads heavy index fragmentation.

Let me demonstrate with some examples. Here I am using the following Sql server version. However the scripts will run on 2008 R2.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64)

Jul 25 2014 18:52:51

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Shrink database causes heavy index fragmentation. when sql server try to free up space, it moves pages from end of the file to beginning of the file.

/*

Author: Hari Prasad Ere

Sub: Shrink DB file demo.

*/

--Create database for demo.

IF DATABASEPROPERTYEX (N'DbShrinkTest', N'Version') IS NOT NULL

    DROP DATABASE [DbShrinkTest];

GO

CREATE DATABASE DbShrinkTest;

GO

USE [DbShrinkTest];

GO

-- supress messages

SET NOCOUNT ON;

GO

 -- Create test tables

CREATE TABLE [Wine] (

    [id] INT IDENTITY,

    [Winetype] CHAR (8000) DEFAULT 'Malbec');

GO

-- Fill up the wine table by excuting go batch 3000 times

INSERT INTO [Wine] DEFAULT VALUES;

GO 3000

-- Create the product table

CREATE TABLE [ProdTable] (

    [ID] INT IDENTITY,

    [ProdName] CHAR (8000) );

CREATE CLUSTERED INDEX [prod_ID] ON [ProdTable] ([ID]);

GO

-- load product table from product adventure works database by excuting go batch 10 times

INSERT INTO [ProdTable] (prodname)

SELECT [Name] from AdventureWorks2014.Production.Product

GO 5

-- select * from wine

--select * from prodtable

-- Check the fragmentation of the production table

SELECT

    [avg_fragmentation_in_percent]

FROM sys.dm_db_index_physical_stats (

    DB_ID (N'DbShrinkTest'), OBJECT_ID (N'ProdTable'), 1, NULL, NULL);

GO

-- avg_fragmentation is returned on my server is 0.396825396825397.

-- Now lets start test drop the wine table and run shrink

DROP TABLE [Wine];

GO

-- Shrink the database

DBCC SHRINKDATABASE ([DbShrinkTest]);

(Note: shrink file also leads index fragmentation).

GO

-- Check the fragmentation of the production table

SELECT

    [avg_fragmentation_in_percent]

FROM sys.dm_db_index_physical_stats (

    DB_ID (N'DbShrinkTest'), OBJECT_ID (N'ProdTable'), 1, NULL, NULL);

GO

-- avg_fragmentation is returned on my server is 99.9206349206349.

-- cleanup

IF DATABASEPROPERTYEX (N'DbShrinkTest', N'Version') IS NOT NULL

    DROP DATABASE [DbShrinkTest];

GO

Workarounds

1. If your environment is required to shrink, then  REORGANIZE / REBUILD indexes after the SHRINKFILE/Shrink DB.

   2)    Add a new file group, Move user tables and indexes into new file group. Build indexes on new file group and shrink the file on old file group.