How to shrink MSSQL Database?

Important Note: We don't recommend to shrink your database. You can involve your Database administrator to perform this action. Before you make any changes, please make sure you have taken the backup of your Database which you are going to shrink. You will face an issue if the Backup is not available.

Database administrators may face an issue where their SQL Database becomes large in size. In this case, DBA will need to shrink the Database and allocate free space to your SQL server. Before you proceed with the Database shrinking, you must need to know about the process involving in the Database shrinking.

Shrinking SQL Database is a process of removing the unused space from your Database. You may face an issue with Index fragmentation after shrinking the MSSQL Database. SQL will perform the process where data will be moved from the end of the database file to the unused space and free-up the space at the end of the Database file. The process of shrinking will always move from the end of the file to the front side.

Option 1 : Shrink Database from Microsoft SQL Management Studio :

Here, are the steps to shrink the Database from MSSQL Management Studio.

  1. Login to your SQL Server with MSSQL Management Studio.

  2. Right Click on the Database which you need to shrink >> Tasks >> Shrink >> Database.



  3. Click on the OK button.



Option 2 : Shrink Database Using Transact SQL Command :

  1. Login to your SQL Server with MSSQL Management Studio.

  2. Click on New query.



  3. Hit the below query by changing your database name and target percentage.

    DBCC SHRINKDATABASE (DatabaseName, target percent);
    


    This is how you can shrink your Database.

  • 0 Users Found This Useful

Was this answer helpful?

Related Articles

How to Backup MSSQL Database from SQL Server Management Studio?

To take MS SQL database backup, first you need to connect to MS SQL Server through SQL Server...

Comparison of MS SQL Server 2016 Express Edition V/S. MS SQL Server 2016 Web Edition

Following is a brief comparison between MSSQL Server 2016 Express Edition and MSSQL Server 2016...

How to change Database Collation in MsSQL Server?

Collation is a configuration level setting in the MSSQL. It directs how the character data will...

How to Change MS SQL Database Recovery Model?

This article describes how to to change recovery model of a MS SQL database through SQL Server...

How to identify and fix the orphan users in MSSQL Server?

Orphaned user in MSSQL means that a user who is present at the database level but the relevant...