Recently, my team got a requirement to reduce the size of one of our SQL Server databases to boost the performance of that DB. As a team, we analyzed this requirement and identified a few ways to do this. In this article, I will walk you through multiple ways to shrink a database in SQL server.
How To Shrink DB In SQL Server
Let us dive deep into both approaches individually.
Approach-1: Using DBCC SHRINKDATABASE Command
The primary approach is to use the DBCC SHRINKDATABASE Command to shrink the SQL server database.
Syntax
DBCC SHRINKDATABASE (DatabaseName, target_percent)Example
In this example, we will shrink the Test_Schema database by 10 percent.
DBCC SHRINKDATABASE (Test_Schema, 10)After executing the above query, I got the expected output below.
Check out Table Partitioning In SQL Server With Example
Approach-2: Using SQL Server Management Studio (SSMS)
Follow the below steps.
1. Connect to SQL Server Management Studio (SSMS) and right-click on your database name.
2. Select Tasks –> Shrink –> click on the Database option.
3. Choose the appropriate options based on your requirements and click the OK button, as shown in the screenshot below.
Check out How To Count Number Of Columns In A Table In SQL Server
Approach-3: Using Shrinking Specific Files with DBCC SHRINKFILE
We can also use the DBCC SHRINKFILE command to shrink a specific file in the SQL server database.
Syntax
DBCC SHRINKFILE (logical_file_name, target_size)Example
Below is an example where USAdb_report is the file we are trying to shrink with the target size of 1024.
DBCC SHRINKFILE (USAdb_report, 1024)Conclusion
Shrinking a Database In an SQL Server, it is crucial to reduce the size of the SQL server database in terms of performance. You can use any of the approaches mentioned in this article to achieve this.
You may also like following the articles below.
- How To Create A User In SQL Server Database
- How To Check If Column Exists In SQL Server Database
- How To Enable SA Account In SQL Server
Grey is a highly experienced and certified database expert with over 15 years of hands-on experience in designing, implementing, and managing complex database systems. Currently employed at WEX, USA, Grey has established a reputation as a go-to resource for all things related to database management, particularly in Microsoft SQL Server and Oracle environments. He is a Certified Microsoft SQL Server Professional (MCSE: Data Management and Analytics) and Oracle Certified Professional (OCP), with Extensive database performance tuning and optimization knowledge and a proven track record in designing and implementing high-availability database solutions.