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
Machine generated alternative text:
is Messages 
Dbld Fileld CumentSize 
Minimum Size 
used P ages 
Estim at ed Pages
— 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.
Machine generated alternative text:
Resuğs Messages 
1 7ğğ206349206ü9 J
— 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s