Shrinking mdf files

Hi,
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,
PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

x-menIT super heroCommented:
files added to a filegroup will be populated as needed.

Files and Filegroups Architecture:
http://technet.microsoft.com/en-us/library/ms179316(v=sql.105).aspx
0
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:

http://technet.microsoft.com/en-us/library/ms190447%28v=sql.105%29.aspx
http://stackoverflow.com/questions/6267273/how-to-restore-to-a-different-database-in-sql-server
0
dwkorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.