Shrink Data File (.MDF) 

--YES its possible;

# LEARNING #New Skill #Task>Challenge

My Recent encounter to a request from client to split the database data file (.Mdf) into 2 datafiles got me this new challenge and a great learning . I would like to share the experience and the technicality behind this theory.
As a DBA we have many times used the command DBCC shrink to get out target results. But there are different parameters that are very useful.
So let’s try to shrink the MDF file. Let’s start the steps.

Step 1: Take the backup of the existing database.(Play it safe..!)

Step 2: Check the disk usage report.  Follow steps as per below snapshot:

Right click the database >reports>Standard Reports>disk usage report.

Note: You need to ignore the free space in the data file. (Every database keeps some free space to perform some maintenance operations for more follow the blog from Brent.)

Step 3: Now add another file in the database:

Step 4: Now check again the Disk usage report

Step 5: Now we run a DBCC SHRINKFILE with EMPTYFILE parameter and it will automatically start distributing data to the new files.

--Move Data to the blank File Added Recently

use[Adventureworks2014]
Go
DBCC SHRINKFILE(N’Adventureworks2014_Data’,EMPTYFILE)
GO

Step 6: Now check again the Disk usage report:

NOTE; Here you need to ignore the space consumed by the data file because that’s the file space reserved as the initial size. 

This is how you can split the data file. To be sure of the results and consistency of the database I ran the DBCC CHECKDB command.

For more details about the multiple file database performance gains follow blog from Paul. 

Thanks for your attention: follow me on-Linkedin

Post your suggestions and comments :