How to reduce the mdf file size

When we shrink a data file, SQL Server first move pages at the beginning the file. This process frees up the space at the end of the file & the file can be shrunk.  There are two commands to shrink a database file, first is DBCC SHRINKDATABASE which targets all files for the database & the other one is DBCC SHRINKFILE which targets a particular database file. You should always preferred DBCC SHRINKFILE if you want to shrink a single file. You can also shrink the database via the Management Studio. First you should check the free space that you have in the database by running "sp_spaceused" in the database that you need to shrink. Here is full syntax:

sp_spaceused [[ @objname = ] 'objname' ] 
[,[ @updateusage = ] 'updateusage' ]
        
If there is free space in the database than you can use following methods to shrink you MDF file:

1.Use Object Explorer in SQL Server Management Studio to shrink the mdf file:

• Object Explorer>>connect to an SQL Server Database Engine instance>>expand that instance.
• Expand Databases>>right-click the database that you want to shrink.
• Tasks>>Shrink>>Files

• Select File Type>> File name
• Optional: Click on 'Release unused space' check box.
• Optional: enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
• Optional: Click on the 'Empty file by migrating the data to other files in the same filegroup' check box. You can use this option to move all your data from the specified file to other files in the same filegroup.
• Click OK.

2. Run a shrink file using the DBCC shrinkfile command: 

DBCC SHRINKFILE 
(
{ file_name | file_id } 
{ [ , EMPTYFILE ] 
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]

NOTE: The database cannot be made smaller than the minimum size of the database that is specified when the database was originally created. 

Some Remarking Points:

• You can apply DBCC SHRINKFILE to the current database files.
• You can stop DBCC SHRINKFILE operations at any point in the process.
• The DBCC SHRINKFILE operation shows an error message when it get failed.
• The database should not be in single-user mode when it being shrunk.
• Other users can be work on the database when the file is shrunk. 

Shrinking a database should be a rare operation and should not be part of your regular database maintenance. Heavy shrinking and growing of database files will fragment your file system, which will further hurt SQL Server performance.