Database Shrink in SQL Server 2012

Database shrink is used to recover the free space of data and log files of specific database. While we executing database shrink command, internally it moves the data pages from the end of the file to unallocated space which is closer to the beginning of the file. Once free space is created at the end of the file, it starts de-allocating data pages at the end of the file.

Shrink operation does not preserve the fragmentation status of indexes. Usually frequent database shrink will increase the degree of index fragmentation which may lead to performance issue. 

Frequent database shrink operation and AUTO_SHRINK option are not recommended for database health. It is also recommended to rebuild\re-organize the index, after each database shrink operation to improve the database performance factors.

During Database Shrink operation, database size cannot be reduced smaller than the minimum size which we set during the database creation. Also if backup process is in progress, then database shrink will not work.

Database Shrink can be performed through both SQL Server Management Studio and T-SQL Script.

Let’s follow the below steps to perform through SQL Server Management Studio

1.   Connect to the SQL Server Instance.

2.   Right click on specific database – Click on “Task” – Shrink – Database.

a)   Database – Shows the name of the selected database

b)   Current Allocated Space – It tells us that total used and unused   space of the selected database.

c)    Available Free Space – It displays the available free space in the data and log files of selected database.

d)   Shrink Action – Reorganize files before releasing unused space

[

If it is checked, then it is equivalent to DBCC SHRINKDATBASE with specifying target percent option.

If it is un-checked, then it is equivalent to DBCC SHRINKDATBASE with TRUNCATEONLY option.

]

e)   Maximum free space in files after shrinking – Enter free space percentage to be left after the database shrink operation.

3.   Click OK.

Screen shots for these steps have been captured for the better understanding.

 

Now let’s go through, how it can be performed through T-SQL Script.

This script is decreasing the size of the data and log files of mentioned database and make sure that 5% of free space left on each files.

USE [master]

GO

DBCC SHRINKDATABASE (<Database Name>, 5);

Below script has been tested before and after the shrink operation.

-- Size of the database before Database Shrink operation

USE [Test_Contained_DB]

GO

SELECT file_id, name, type_desc, physical_name, size, max_size

FROM sys.database_files ;

GO

-- Shrink Database

USE [master]

GO

DBCC SHRINKDATABASE (Test_Contained_DB, 5);

-- Size of the database after Database Shrink operation

USE [Test_Contained_DB]

GO

SELECT file_id, name, type_desc, physical_name, size, max_size

FROM sys.database_files ;

GO

Refer size of the database before and after the Shrink operation from the below mentioned screen shots.