How to Shrink MDF File in SQL Server

Stephen West | May 4th, 2016 | SQL Master Database |

Overview

SQL Server is a relational database management system, developed by Microsoft for the purpose of storing and retrieving data when required. Each database on SQL Server consists of a Master Data File (MDF) that may be associated with multiple New Data files (NDF) and a separate transaction log database file (LDF). The blog has been focused to guide users on how to shrink MDF File in SQL Server using different ways.

When to shrink MDF File?

Shrinking Operation is used to recover space by removing pages an empty space from database files and release the empty space either to the Operating System or to SQL Server. Shrinking does not mean compressing the file.

  • Shrink Option is effective after an operation that creates lots of unused space like drop table or truncate table operation.
  • If you shrink the database repeatedly and sees that the database size grows again after shrinking, it means that the space that was shrunk is needed for regular operations. Repeatedly shrinking the database is a wasted operation.
  • Repeatedly shrinking the database increases fragmentation to a degree as it does not preserve the fragmentation state of indexes in the database.
  • AUTO_SHRINK database option should not be kept ON unless there is a specific requirement.

How to Shrink .mdf File in SQL Server

  1. Using SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Management Studio, Go to Databases
    • Select the desired database that needs to be shrunk
    • Right-click on the database, Select Tasks >> Shrink >> Files
      Shrink MDF File
    • Make sure that you choose Data File type for shrinking MDF File
      Select File Type
    • The options of Shrink Option are as follows:
      • Release Unused Space
      • Reorganize pages before releasing unused space and Shrink File to desired size — in MB (Minimum is 3MB). Since the minimum data file size is 3MB, the shrink file size should be equal or more than 3MB while for log file it can be 0 MB.
      • Empty File by migrating the data to other files in the same group
    • After the backup of the log file is taken, there will be unused space. So, the release Unused space is often used while shrinking the log file.
    • Second shrink option is advisable for shrinking the Master Data File (MDF). Select this option ‘Reorganize pages before releasing unused space’ and Click on OK
  2. Using T-SQL Script

For shrinking MDF file using T-SQL script, following commands will be used.

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, 3)
GO

Demo is the database name and 3 MB is the size to which the file will be shrunk.

Execute the command to shrink the MDF file.

Command to Shrink MDF File

Conclusion

The Blog discusses about the scenarios when the user of SQL Server should use the shrink operation. The shrink option is not recommended unless it is required like truncate table that is no longer needed to retrieve the space. The shrinking the Master Data file (MDF) as discussed is possible in two ways either by using shrink option in SSMS or by using command on T-SQL Script. Reorganizing the indexes after shrink operation may be necessary as it has bad impact on the indexes of the database and may lead to slower performance.