• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Shrinking mdf files

I have a question related to space on drives and size of mdf files.

I have very little space left on one of my drives which holds some of my mdf files only (log files on another drive)

Rather then shrinking the mdf to avoid fragmentation - I was wondering what options are best to approach.

Currently the database has only one filegroup for the data file.
Would it help to create another data file for the database on another drive? would this automatically start loading the data to this filegroup, and once the two mdf (data) files get to the same size, would they start sharing the loading?

thank you,
3 Solutions
x-menIT super heroCommented:
files added to a filegroup will be populated as needed.

Files and Filegroups Architecture:
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
I would backup the database, move it to another drive by restoring it (change the path when prompted).  Refer to links below:

Generally speaking, SQL Server uses proportional fill algorithm when filegroup has multiple data files. More free space data file has, more writes it would handle.

Technically you can add another data file to the filegroup on the different disk although SQL Server would still put some data to the original file. It is also questionable in terms of performance in case if you drives are different in terms of speed and unless you have mirrored raid, it would increase the chance that DB would be corrupted if one of the disks fails.

One of the options you have is creating another data file on another disk and empty the original one by using DBCC SHRINKFILE(<old file>,emptyfile). This will move all your data to the new file keeping your database online. After that you can remove original file from the filegroup. The biggest downside of this method is that introduce huge fragmentation and a lot of tran log activity. Make sure to REORG your indexes after you done - do not REBUILD - just REORG, otherwise the size of data files would grow up again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now