Shrink Database File (mdf) In SQL Server

In my previous article, I’ve explained the methods to shrink the transaction log files in SQL Server without disturbing the database files. In this article, I’ll explain the methods to shrink database file (.mdf). Just like shrinking transaction log, there are multiple ways to shrink the data file. But make sure you are not to shrink the data file frequently or have the shrink operation in a the maintenance plan. In fact, try to avoid shrinking the database data file.

The easiest way is to use the DBCC SHRINKDATABASE transact-sql method to shrink just the data file alone. The next method is to use the DBCC SHRINKFILE transact-sql. Another way is to use the Shrink File GUI in SSMS. I’ll go through these methods one by one. Before shrinking the data file, be aware of the best practice and follow them.

Best Practice

• Shrinking data file should never be a maintenance plan. Try to avoid shrinking the data file. Shrinking data (mdf) file is a bad idea. It will dramatically increase the fragmentation in the data file.
• The only scenario which you may need to shrink database file is after removing / deleting huge amount of data from the database, and if the free generated by data deletion is more than enough for growth.
• If at all you have to shrink the database data file, take backup of the database before shrinking the database data file.
• While shrinking, leave enough unused space in the database file so as the data base should not grow often to accommodate data.

DBCC SHRINKDATABASE (Transact-SQL)

Query Template

USE {{Database Name}}
GO
DBCC SHRINKDATABASE ({{Database Name}})
GO

Example

USE WideWorldImporters
GO
DBCC SHRINKDATABASE (WideWorldImporters)
GO

DBCC SHRINKFILE (Transact-SQL)

1. Get the name of the transaction log file name using this query.
   

   Query Template

   USE {{Database Name}}
   GO
   EXEC sp_helpfile
   GO
   

   Example

   USE WideWorldImporters
   GO
   EXEC sp_helpfile
   GO
   

   
   In this example WWI_Primary and WWI_UserData are the database files of this database.

2. Now execute DBCC SHRINKFILE. Include the log file name in the shrink file query. The target size is optional. But it’s good to specify a target size to give a reasonable free space for regular operations.
   

   Query Template

   USE {{Database Name}}
   GO
   DBCC SHRINKFILE('{{Log File Name}}', {{Target Size in MB}})
   GO
   

   Example

   USE WideWorldImporters
   GO
   DBCC SHRINKFILE('WWI_Primary', 10)
   GO
   DBCC SHRINKFILE('WWI_UserData', 10)
   GO
   

   

Using SSMS GUI

Shrink Database Method

1. Login to SSMS.
2. In the Object Explorer, expand the Databases folder.
3. Select the database whose log file you want to shrink.
4. Right click the database and select Tasks >> Shrink >> Database.
   
5. In the Shrink Database window, under the Shrink action section, select the option Reorganize pages before releasing unused space.and enter the percentage of space you want to leave free.
   
6. Click Ok. This will shrink database file.

Shrink File Method

1. Login to SSMS.
2. In the Object Explorer, expand the Databases folder.
3. Select the database whose log file you want to shrink.
4. Right click the database and select Tasks >> Shrink >> Files.
   
5. In the Shrink File window, choose the file type Data from the File Type drop down box.
6. In the Shrink action section, select the option Reorganize pages before releasing unused space.and enter the space you want to leave free.
   
7. Click Ok. This will shrink database file.

Reference

• About shrinking a database in MSDN.
• About DBCC SHRINKFILE in MSDN.