Reduce SQL Server Database Size (Shrink Command + Fixes)

In this article, we explain how to reduce or shrink the SQL Server database size safely and effectively.

Important Instructions Before Shrinking the Database:

You cannot shrink a database while a backup is in progress, and you cannot take a backup while the database is being shrunk.
A database cannot be shrunk to a size smaller than its original (initial) size. For example, if a database was initially 10 MB and later grew to 2 GB, the minimum shrink size will still be 10 MB.
Ensure sufficient free space is available on the disk before performing the shrink operation.

Set Database Recovery Model to Simple (Recommended)

Before changing the Recovery Model to Simple, ensure you understand the impact on transaction log backups and recovery requirements. If the database is switched back to Full recovery mode after shrinking, a new Full Database Backup should be taken before resuming transaction log backups.

Steps to Set Recovery Model to Simple:

Open SQL Server Management Studio (SSMS).
Right-click the database and select Properties.
Go to Options.
Set Recovery model to Simple.
Click OK.

Note:

After switching the database back to Full Recovery mode, take a new Full Database Backup before resuming transaction log backups, as the previous log backup chain is no longer valid .

Method 1: Using SQL Server Management Studio

Right-click on the Database name >> Tasks >> Shrink >> Database.
Then Click OK.

Method 2: Using Transact SQL Command

Open SQL Server Management Studio.
Connect to the Database Engine.
From the Standard bar, click New Query.
Type the following SQL command into the query window and click Execute.
```
DBCC SHRINKDATABASE (DatabaseName, 'target percent') --
```
Example:
```
DBCC SHRINKDATABASE (test, 10);
GO
```
This command shrinks the database named test to leave 10% free space.