sql server and when adding files and capping space

Question

Post reply

sql server and when adding files and capping space

tcroninlifepoint

SSC Veteran

Points: 235
More actions
September 2, 2021 at 6:42 pm

#3926382

so I inherited a mess of a sql server 12 drives on primary file group, last 2 that looks like they were added about to run out of space, none of the files were capped for growth. My solution was to add another ndf and move a couple large objects over. I was under assumption if you capped growth and added a new ndf sql would roll over to that one but this one has no caps on space so guessing they must have manually moved objects

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

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

Do you have a q?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

It's hard to tell.... are you looking for confirmation about your "assumption" or ???

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

Make sure you understand the difference between adding a new filegroup vs just adding a new ndf to the primary file group.

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 4

Even when adding a new file to a filegroup, SQLServer will not balance the content of that filegroup to all files of that filegroup just like that.

If you would want to have all the data nicely balanced over all files in that filegroup, you would at least have to rebuild all indexes that reside in that filegroup. LOBs are not affected by that operation.

e.g. if you need to add a (couple of ) file(s) to an existing filegroup, but find that the balance is an absolute must, you should add an extra filegroup that can hold all the data of the original filegroup, move over all objects of that filegroup to this temporary filegroup, then add the files to the original filegroup, make all these files of the same size ( probably shrinking the existing file(s) ) , and move all objects back to the original filegroup.

Also keep an eye on the log file ( size / consumption ) during such operation or maybe switch to simple logging during the operation if that is allowed !

Start with a full backup and end with a new full backup ! ( no matter if you change recovery settings ! )

Keep in mind to put the dbs recovery setting back to what it was ( may need a new full backup ! )

( I've performed such operation on a system that auto-creates its objects at runtime and always uses "primary" filegroup )

This is an "expensive" operation and my need a vast amount of time ! ( moving LOBs is painfully slow )

DBCC shrinkfile ( x, emptyfile ) is a slooooooooow process, and will produce an error for the initial primary data file !

It will move the data, but it cannot move the systems catalog objects !

Shrinking the existing filegroup files to "force" balancing is even be slower !

After any shrink operation, rebuilding indexes in that filegroup is strongly advised ! ( pre-size the files after the shrink operation )

Test it and plan enough down time for such operation !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me