Shrink SQL Server Database Without Compromising Database Performance

Regularly working with SQL Server databases can lead to unmanageable growth and performance degradation. This is why it becomes crucial for database administrators to shrink SQL Server database along with the database files to maintain performance and secure data. With the help of this technical write-up, we will learn more about how to shrink the database and what measures to take to avoid index fragmentation or similar issues during the database compression. So, without any further delays, let’s now proceed with understanding the common reasons for database shrinking. 

Why do DBAs Shrink SQL Server Database? Common Scenarios Explained

When we talk about shrinking SQL Server database files, it refers to the process of reducing the physical file size by reclaiming the unused space from the database files. This process helps users free up disk space by removing data and is only recommended when the reclaimed space won’t be required in the future. Now, here are some of the common reasons that require database file compression:

• When deleting a large amount of data from one or more tables. 
• In case a database grows during imports or testing operations. 
• When the transaction log file expands because of long-running transactions in SQL Server. 
• After deleting or dropping large tables, indexes, or database partitions that aren’t required anymore. 

These are some of the common situations that demand database administrators to shrink SQL Server database. We will now take a look at the methods that will help users with the database compression process. 

How to Shrink SQL Server Database? All Methods Explained

There are a few methods available to shrink SQL databases depending on users’ requirements, whether they wish to reduce the size of the entire database, compress SQL database files individually, or shrink SQL Server transaction logs. Let’s take a look at all these methods now, one by one. 

Method 1: Shrink Entire SQL Database at Once

In this method, we will discuss the steps for shrinking SQL Server database completely. This method helps users reduce the size of all data files in the database while freeing up unused space. Below are the steps for this process:

1. Open SQL Server Management Studio. Connect to the SQL Server instance. 
2. Expand the Databases option and right-click on the desired database. 
3. Then, move to Tasks and choose the Database option.  
4. Next, review the current and available free space in the specified SQL database.
5. Lastly, click the OK button to start compressing the database file. 

This is how users can shrink an SQL database with the help of SSMS. However, if users are technically aware and can use the commands precisely, below is the T-SQL command to shrink SQL Server database files effectively:

T-SQL Command for SQL Database Compression:  

DBCC SHRINKDATABASE
(
 Database_Name,
 10
);

Moving ahead with the next method, let’s take a look at how database administrators can shrink database files(.mdf/.ndf) individually.

Method 2: Shrink SQL Database MDF or NDF Files Individually

With this method, we will discuss how database administrators or developers can shrink their MDF or NDF files separately depending on their requirements. Below are the steps that will help users with the process:

Step 1: The first step is to find the logical file name. This can be done using the given command:

SELECT name, physical_name
FROM sys.database_files;

Step 2: The next step is to shrink the data file. We will use the following command for the same:

(
DBCC SHRINKFILE
20480
);

If users know how to execute T-SQL commands without errors, this method can help them shrink the file. However, if they aren’t technically aware of the code, it is better to avoid it and use SSMS for a simpler way out. Below are the steps for the process.

1. Open SSMS and connect to the SQL Server instance. 
2. Select Tasks and move to the Shrink option. 
3. From the Files option, select MDF or NDF files, respectively. 
4. Set a target file size to shrink SQL Server database.
5. Lastly, click the OK button to initiate the process. 

These steps will allow users and database administrators to carry out the entire operation seamlessly. Let’s now take a look at how to shrink SQL Server database transaction log file. 

Method 3: Steps to Shrink SQL Server Transaction Log File

The reason behind growing transaction log files in SQL Server is slow-running queries. We will now go through the steps that will allow users to reduce transaction log file size with precision. 

Step 1: The first step is to identify the log file for the compression process. Here is the code for the same:

SELECT name, type_desc
FROM sys.database_files;

Step 2: Next, take a complete backup of the transaction log file to avoid data loss during the database shrink process. Below is the command to take a backup:

BACKUP LOG Database_Name
TO DISK = 'D:\Backup\Database_Name_Log.trn';

Step 3: Once this is done, use the given command to shrink the transaction log file in SQL Server.

DBCC SHRINKFILE
(
Database_Name_Log,
2048
);

These steps allow users to reduce the size of the desired database and database files while ensuring precision and efficiency. After learning all the steps, we need to keep a few things in mind to avoid data loss during the file shrink process. 

Conclusion

With the help of this write-up, we have learned how to shrink SQL Server database in a hassle-free way. To understand the methods precisely, we have discussed the common scenarios that require users to reduce the size of SQL database files, and also the complete steps of each method are explained. In case things go sideways, users can always use a dedicated recovery solution to restore the affected data safely.