Home » SQL Master Database » How to Shrink MDF File in SQL Server
SQL Server Recovery Manager

How to Shrink MDF File in SQL Server

Stephen West ~ Modified: December 22nd, 2023 ~ SQL Master Database ~ 7 Minutes Reading

how-to-shrink-mdf-file-in-sql-server

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 to shrink MDF file in SQL Server also known as .mdf using different ways.

Table of Content

Can We Shrink MDF File SQL Server – Critical User Queries

Before we proceed further to find out the solution along with other crucial aspects,  we must understand why is it a problem for users. This simple task to reduce MDF file size is not that tough but a little technical. Below are the common queries of users throughout the technical communities asking can we shrink MDF file SQL Server & if yes how.

SQL Server 2008 MDF Shrinking Query

shrink MDF 2008

Space Not Increased After Shrinking MDF

size not increase after shrink mdf

Got Error in Shrinking File

shrink MDF error

After, understanding these queries, some of the most common answers that users suggest are:

  • Users mistakenly use SHRINKDATABASE instead of SHRINKFILE.
  • Not putting the database in the right mode/state often causes errors.
  • Use the below command:

    USE DB_Name
GO
DBCC SHRINKFILE (N 'yourdatabaseName', 3)
GO
  • Don’t forget to sort out the index fragmentation caused by file shrinking.

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.

Create a Test Environment for SQL Server Shrink MDF File

Before users opt for the actual operation, they must run a pilot project. However, this is only required for the manual command line process. The significance of this method is utmost for expert users. However, new users are most likely to face errors. Let’s understand the best way to do so.

First of all, let’s make a new sample database:

USE SampleDB;

GO

DECLARE @rdate DATE

DECLARE @startLoopID INT = 1

DECLARE @endLoopID INT = 10000000 -- Amount of Rows for addtion

DECLARE @i INT = 1

WHILE (@i <= 10000000) -- Make this same as the "@endLoopId" from above

WHILE @startLoopID <= @endLoopID

BEGIN

    SET @rdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 7300 ), '2001-05-24'); -- The "7300" represents 20 years, the date provided is the starting date.

    SET @startLoopID = @startLoopID + 1;

    INSERT INTO Sales.Customers(CustomerID, CustomerName, CustomerAddress, Comments, LastOrderDate)

   VALUES 

   (

      @i,

      'CustomerName' + CAST(@i AS CHAR),

      'CustomerAddress' + CAST(@i AS CHAR),

      'Comments - write comments here if any' + CAST(@i AS CHAR),

      (@rdate)

   )

   SET @i += 1;

END

Now, after creating this database, the file size has become more than 4GB and it reduced the drive space which was earlier 250GB to 245GB. Approx 5GB is now increased in total.

SHRINK FILE

Now, simply use the manual T-SQL command as follows to shrink MDF file:

USE SampleDB;
GO
-- Shrink the mdf file
DBCC SHRINKFILE(N'SampleDB', 0);
GO
-- Shrink the log.ldf file
DBCC SHRINKFILE(N'SampleDB_log', 0);
GO

How to Shrink .mdf File in SQL Server Using SSMS

  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

Command to Shrink MDF File

Our Recommendations for Users

We recommend users go through the below considerations before they opt for the Shrink file task in SQL Server database. It can be beneficial for them to learn how to shrink MDF file in SQL Server with a wise approach.

  • Users should the Shrik operation only after a task that ends up creating a large amount of data in the database that remains unused. For example: DELETE or TRUNCATE operations.
  • Database often requires a specified space for regular operations happening in the database on a day-to-day basis. Repeatedly, using the SHRINKFILE operation is a waste in such cases.
  • Index fragmentation is another issue that users need to tackle once they learn how to reduce MDF file size without any hassles. Otherwise, slow performance and other issues are there to trouble them.
  • It is not recommended for users to set the AUTO_SHRINK option unless they have a specific requirement based on their tasks.
  • The Shrink task takes time more than expected. Therefore, users must plan this during the off-beat hours. This can help them minimize the effect on the regular workflow to a great extent.
  • In case the IO system is forced, IO timeouts might happen and result in long disk queue lengths.

Limitations to Shrink MDF File in SQL Server

There are not many limitations to this task. However, depending on the method, & situation, we have got some points to remember:

  • The manual methods are not the sure shot solutions & might have errors. Evidently, users must make the method selection decision wisely.
  • It should be noted that there is no way one can make the primary data file smaller in size than that of the primary file in the model database.
  • Users might end up corrupting their SQL server indexes, if not done the procedure right. To counter it, we recommend users to opt for SQL recovery solution.

Download Now Purchase Now

Conclusion

The Blog discusses about the scenarios when the user of SQL Server should use the shrink operation. The shrink MDF file 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. Evidently, 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.