point all transactions and activity to new database file so old one ceases to grow

Question

point all transactions and activity to new database file so old one ceases to grow

Matthew.Peters 13064

SSC-Addicted

Points: 460
More actions
June 30, 2014 at 11:53 pm

#296123

Hello
Our mdf file is growing at a rapid rate and we are a running out of space on the drive that hosts it. As a backup plan I wanted to create a new ndf file and point all new activity to this file so the old one ceases to grow. I know how to create the new file however I don't know how to point all new activity towards it, SQL server seems to split the activity over the 2 files.
Does anybody know how to do this?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

I guess I wouldn't try such a thing. I believe that if you simply add another file to the file group, it will use that new file until both files balance.

--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)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Jeff Moden (7/1/2014)
I believe that if you simply add another file to the file group, it will use that new file until both files balance.

Unless the first file is full, no it won't. It's called proportional fill algorithm and will still fill both files just at a different rate.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Matthew.Peters 13064 SSC-Addicted Points: 460 More actions · Answer 3

Thanks for the replies, I actually got around this by adding another databases file to the primary filegroup on a new disk with unlimited growth, then limiting the growth on the original file. The original file used up it's allocated space then stopped growing, all the new growth started happening on the new file