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


4 comments:

  1. I was exactly searching for. Thanks for such post and please keep it up. Great work. We are extremely thankful for your blog entry. Best Singing Classes in Delhi | Fashion Designing Institute in Delhi | Makeup Courses in Delhi | Event Management Institute in Delhi | Interior Design Course in Delhi

    ReplyDelete
  2. Very nice collection of blog you have shared here so interesting and informative so thank you very much for sharing such kind of blog.
    Coaching Centres in Rohini | Chemistry Coaching in Rohini | Economics Classes in Rohini | Accounts Tuition in Rohini

    ReplyDelete
  3. Thank you so much for giving us all of SQl DBA video and others tips .It is great for regenerate knowledge with practical.

    ReplyDelete
  4. Thanks for the detailed information. When I work with database I try to found different useful tools. The one of them can connect to postgresql .net and has good features.

    ReplyDelete