How to reduce/shrink the SQL server database size October 14, 2022

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 shrinking the database, it is recommended to set the Recovery Model to Simple, as shrinking may not work effectively in Full recovery mode.

Method 1: Using SQL Server Management Studio

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

Method 2: Using Transact SQL Command

1. Open SQL Server Management Studio.
2. Connect to the Database Engine.
3. From the Standard bar, click New Query.
   
4. 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.

   

That’s it! By following these steps, you can safely shrink the SQL Server database size.

If you found this article helpful, you will definitely appreciate our support services.

Also Read: How to Backup a MySQL Database using phpMyAdmin