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
- 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.
