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

sql server - shrink database file 01

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
    

    shrink transaction log file 02
    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
    

    sql server - shrink database file 02

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.
    sql server - shrink database file 05
  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.
    sql server - shrink database file 04
  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.
    shrink transaction log file 04
  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.
    sql server - shrink database file 03
  7. Click Ok. This will shrink database file.

Reference

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

 


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.