What are the options for splitting/reducing large mdf files?

Question

What are the options for splitting/reducing large mdf files?

azenon

Ten Centuries

Points: 1162
More actions
June 4, 2013 at 7:37 am

#294137

I have a database with 2 mdf files. One file is 750gb and the other file is 112gb. There are performance issues with backing up this database and the database will continue to grow larger.
What are the options in looking into managing large data files? Is there some mechanism for splitting it up?
Any input is appreciated!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

There's no easy way to split an existing datafile. There are a few options, but keep in mind that most of them are very I/O and CPU consuming.

- add additional filegroup(s) with multiple presized files. Rebuild the clustered indexes of several (or all) tables to the new filegroup.

- create additional (presized) datafiles within the initial filegroup. Shrink the existing datafile by using DBCC...EMPTYFILE.

This last option is NOT the preferred way. It will generate a lot of defragmentation and will take a long time to complete. Better go with the first option.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 2

You can also try partitioning.

If you partition by date/week/month, etc, and some older partitions are never updated, you can backup whole database once, and then only last, active partitions. It will reduce your backup time.

J Good Default port Points: 1435 More actions · Answer 3

Paul Randal had a good, quick article on this for SQLMag...

http://sqlmag.com/blog/rebalancing-data-across-files-filegroup

Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]

bcaruso 14201 SSC Veteran Points: 272 More actions · Answer 4

Thank you, J_Good, I've been searching SSC about a week for this information! Another excellent article from Paul Randal!

J Good Default port Points: 1435 More actions · Answer 5

No problem! Thank Paul Randal, not me.

Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

azenon (6/4/2013)
I have a database with 2 mdf files. One file is 750gb and the other file is 112gb. There are performance issues with backing up this database and the database will continue to grow larger.
What are the options in looking into managing large data files? Is there some mechanism for splitting it up?
Any input is appreciated!

Do you have any large "audit-like" tables where rows are only inserted and never updated?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tim_harkin SSCrazy Points: 2394 More actions · Answer 7

J Good (7/9/2014)
No problem! Thank Paul Randal, not me.

I do every day 😀

bcaruso 14201 SSC Veteran Points: 272 More actions · Answer 8

Jeff,

Is your reply to azenon or the thread in general?

I replied on this old thread rather than starting a new one because it looked closest to my situation of being a new DBA with an existing database whose single MDF file needs to be spread across multiple LUNs before upgrading it to SQL Server 2012.

Some of my data is certainly audit-like because it generally isn't updated after getting into SQL Server. The transactional data lives in the clinical side of our healthcare application, and it comes into SQL Server for operational reporting and data retention. I believe our data content could likely be partitioned by date as an earlier post suggested; business and operational units typically look at the most recent 1-3 years of data at a time.

The total scope of my situation is trying to learn, as a brand new DBA, (1) the best means and process to realign a single MDF file across multiple file LUNs, (2) migrating from SQL Server 2008 R2 to 2012, and then (3) moving the entire database onto a new server--items 2 and 3 could always be reversed if there's an advantage to installing 2008 R2 on the new server before upgrading. The first item is the main one where I've been coming up empty handed for solutions although I'm keeping the three aspects in mind in case the database move is the missing link that makes the file split work!

Thanks for any suggestions and pointers to more information!

Becky