DBA Posts - Shrinking SQL Server Database

Before I start on this topic, I want to make everyone aware that lots of considerations need to be taken into account prior to shrinking a database. Below are some facts of shirking database

1. Never Shrink a database unless you absolutely have to
2. Shrinking database can be very costly in terms of database performance
3. Avoid Auto Shrink
4. Avoid Shrinking by planning database usage ahead of time 

First and foremost consideration that you need to take into account is your database performance, shrinking database files or log files might be alright when it comes to TEST, DEV environments, however I would avoid in production environment. Shrinking database will give you unused space back, which might look great at the time being but back behind the scene it is causing high fragmentation, high fragmentation means poor performance of database. We will talk about adverse effects of shrinking database later in this post. Let's talk about first how to shrink the database files.

Shrinking database files in SQL Server 2005  

 a) Using T-SQL:

1: BACKUP LOG SQLAge WITH no_log
2: USE SQLAge;    GO
    DBCC SHRINKFILE (SQLAge_LOg, 10); -- This will reduce the log file size to 10mb.

I would avoid shrinking the entire database, Best way to do it is shrinking the files of a database, log and/or data files separately.

b) Shrinking Database files using management Studio 

Step 1:

BACKUP LOG SQLAge WITH no_log -- SQLAge is your database name , you can use truncate option as well

Step 2:

Right click on the database and go to Tasks > Shrink > Files



Fig 1 - Shrinking Database Files in SQL Server 2005



Select the file type which you would like to Shrink (Data or Log)

Fig 2 - Shrinking Database Files in SQL Server 2005

Click on re-organize pages before releasing unused space option and choose the file size in Shrink File to - Fig 3 shows, shrinking Log file to 10 MB. Then click ok

Fig 3 - Shrinking Database Files in SQL Server 2005

Shrinking database Files in SQL Server 2008 /2012

a) Using T-SQL :

USE SQLAge
GO
 ALTER DATABASE SQLAge -- This will truncate the Log file when you execute this statement
SET RECOVERY SIMPLE
GO
 DBCC SHRINKFILE (SQLAge_Log, 10);--This will Shrink the log file to 10 mb
GO
 ALTER DATABASE SQLAge
SET RECOVERY FULL
GO
 

Note: Keep in mind running above T-SQL will break the Transactional log backup chain, you might want to run FULL backup of the database right after above operation.

b) Shrinking database Files in SQL Server 2008 /2012 Using Management Studio

 Step 1.

Right click on the database that you would like to shrink and go to properties

Fig 1 - Shrinking Database files in SQL Server 2008/2012

Click on options and select recovery mode to Simple and click Ok

Fig 2 - Shrinking Database files in SQL Server 2008/2012

Step 2.

Right click on the database and go to Tasks > Shrink > Files


Fig 3 - Shrinking Database files in SQL Server 2008/2012

Select the file type which you would like to Shrink (Data or Log)



Fig 4 - Shrinking Database files in SQL Server 2008/2012

Click on reorganize pages before releasing unused space option and choose the file size in Shrink File to - Fig 5 shows, shrinking Log file to 10 MB. Then click ok



Fig 5 - Shrinking Database files in SQL Server 2008/2012