Honey I Shrunk The Database

As a developer or a dba you may in your life have to shrink the log file of a database or you will have to shrink the database, because the server that the database is on is running low on space.

The syntax for a shrink statement is:

DBCC SHRINKDATABASE

( database_name | database_id | 0

     [ , target_percent ]  *

     [ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]              

*For more information about how the target_percent or any of the relevant areas of the shrink database query works, reference the following link:

http://msdn.microsoft.com/en-us/library/ms190488.aspx

While using every developer’s good friend “Google” looking for examples on how to use the shrink database statement in sql, I came upon a blog of Brent Ozar with the heading: Stop Shrinking Your Database Files. Seriously. Now.

While reading his blog and clicking on all the links to different sql professional’s opinions about why not to shrink a database the blog of Paul Randal – “Why You Should Not Shrink Your Data Files” explains it well.

To test the theory and to understand it, I copied his code and executed it on my (local) sql server.

The Code that I am using is not code that I have written, I have copied the code from Paul Randal blog.

Changing his database to a test database on my machine:

USE master;

GO

IF DATABASEPROPERTYEX ('QueryTester', 'Version') > 0

 DROP DATABASE QueryTester;

CREATE DATABASE QueryTester;

GO

USE QueryTester;

GO

SET NOCOUNT ON;

GO

-- Create the 10MB filler table at the 'front' of the data file

CREATE TABLE FillerTable (c1 INT IDENTITY,  c2 CHAR (8000) DEFAULT 'filler');

GO

-- Fill up the filler table

INSERT INTO FillerTable DEFAULT VALUES;

GO 1280

-- Create the production table, which will be 'after' the filler table in the data file

CREATE TABLE ProdTable (c1 INT IDENTITY,  c2 CHAR (8000) DEFAULT 'production');

CREATE CLUSTERED INDEX prod_cl ON ProdTable (c1);

GO

INSERT INTO ProdTable DEFAULT VALUES;

GO 1280

-- check the fragmentation of the production table

SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (

    DB_ID ('QueryTester'), OBJECT_ID ('ProdTable'), 1, NULL, 'LIMITED');

GO

The above select statement returns the result:

avg_fragmentation_in_percent

----------------------------

0.546875

-- drop the filler table, creating 10MB of free space at the 'front' of the data file

DROP TABLE FillerTable;

GO

To ensure that the below code does work I have added in an extra little check. Before executing the SHRINKDATABASE statement, execute the following two lines of code:

exec sp_helpfile

name	fileid	filegroup	size	maxsize	growth	usage
QueryTester	1	PRIMARY	22784 KB	Unlimited	1024 KB	data only
QueryTester_log	2	NULL	832 KB	2147483648 KB	10%	log only

exec sp_spaceused

database_name	database_size	unallocated space
QueryTester	23.06 MB	11.05 MB
reserved	data	index_size	unused
11472 KB	10712 KB	688 KB	72 KB

Execute the SHRINKDATABASE and then run the above two lines of code again:

-- shrink the database

DBCC SHRINKDATABASE (QueryTester);

GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	1	1464	288	1456	1456
7	2	72	72	72	72

exec sp_helpfile

name	fileid	filegroup	size	maxsize	growth	usage
QueryTester	1	PRIMARY	11712 KB	Unlimited	1024 KB	data only
QueryTester_log	2	NULL	576 KB	2147483648 KB	10%	log only

exec sp_spaceused

database_name	database_size	unallocated space
QueryTester	12.00 MB	0.17 MB
reserved	data	index_size	unused
11536 KB	10712 KB	688 KB	136 KB

Comparing the before with after we can see that the SHRINKDATABASE query did make the database smaller.

-- check the index fragmentation again

SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (

    DB_ID ('QueryTester'), OBJECT_ID ('ProdTable'), 1, NULL, 'LIMITED');

GO

Before shrinking the database I ran the above select statement and the fragmentation of the cluster index was: 0.546875%. After shrinking the database the fragmentation of the cluster index change to:

avg_fragmentation_in_percent

----------------------------

98.828125

With executing the above code and seeing what happens to the cluster index; and reading Paul Randal’s blog my idea of running a shrinkdatabase statement changed to how can I avoid it?