How to shrink a database in SQL Server

Hello friends, we talked about lot of developer’s topics till date but we haven’t talked about DBA topics a lot, hence I thought to pick one DBA topic today, which is about shrinking of a database. First we will see how we can shrink a database.

Before shrinking a database first of all, we will check the database size which we are going to shrink.

We will use below system stored procedure to check the database size.

Execute Sp_SpaceUsed

If we look at the result returned by the stored procedure in the above screenshot, below are the details

Database Name:- Current database in which we executed the Stored procedure.

Database Size: - Current size of the database (Data file size + log file Size)

Unallocated space:- Space in the database that has not been reserved for database objects.

Reserved:- Total  amount of space allocated by the objects in the database

Data:- Total amount of space used by data.

Index size:- Total amount of space used by indexes.

Unused:- Total amount of space reserved for the objects in the database but not used yet.

SQL Statement to shrink the database:- We can use below SQL statement to shrink the database.

Sample SQL statement (not to run):-

DBCC ShrinkDatabase([DatabaseName]||[Database ID]||0,Target_Percentage,Notruncate||TruncateOnly)

The above DBCC command will shrink the specified database in the DBCC command.

DBCC ShrinkDatabase take three parameters as input , lets understand those parameters first.

[DatabaseName]||[DatabaseID]||0 :-  First parameter is the database name or database id which we want to shrink, if 0 is specified, current database will be shrinked.

Target_ Percentage:- Second parameter is the percentage of free space that you want left in the database file after the database has been shrunk.

Notruncate||TruncateOnly :- If we specify “Notruncate” in the third parameter while shrinking the database, SQL Server moves the allocated pages from the end of data file to the front of the database file in that way it compacts the database size. Notruncate option is only applicable for Data files. The spaced created at the end of the data file is not released for the operating system to use. Hence after shrinking the database, database size doesn't seems to be shrinked.

If we specify “TruncateOnly” in the third parameter while shrinking the database, movement of allocated pages do not happen. In this SQL server release the space from the end of the file and that space is released for the operating system to use. This option is applicable for Log files, there is no impact on Data files. Target_percentage is ignored if “TruncateOnly” is specified.

Lets execute the below DBCC command and see the results.

DBCC ShrinkDatabase(0,Notruncate)

Go

Sp_SpaceUsed

We can see that database size has been reduced from 96 MB to 3 MB and unallocated space has been reduced from 2.68 MB to .79 MB.

I have specified 0 in the first parameter which means we are shrinking current database and second parameter is “Notruncate”.

Things to remember:-

• Shrink is one of the most expensive operation hence we should keep in mind , it should not be done during peak hours.
• Shrink operation do not preserve the fragmentation state of the indexes and generally increase the index fragmentation, hence we should not perform this activity on regular basis.

I hope you will find this article useful for DBA’s. Please feel free to add your comments to give feedback about this article.