1

i have 2 database files under a primary file group, one is set to restricted auto growth (35 GB), the second database file from a same file group but storing in different drive from first one and kept in unrestricted auto growth. is this bad practice ? is both should be symmetric in properties ?

1 Answer 1

1

I wouldn't say it's a bad practice, but definitely not a best practice.

It looks like you're using the primary filegroup for user objects, which is not a best practice. You'd better separate system and user objects.

It also appears that you're storing all the objects in the same filegroup. It might be a good idea to isolate some critical objects from the other ones, in order to put them in a dedicated disk for performance or recoverability reasons. Having multiple filegroups allows you to perform file and filegroup backup/restores, which can help you if you have challenging backup schedules.

If your files were the same size, SQL Server could use the proportional fill algorithm to stripe writes over the files. The storage engine tries to write to files that have the most free space available: you get best results (both files getting written in turn) when the files are the same size.

Autogrowth is nice to have to avoid running out of space when you have unplanned growth, but ideally you should grow your files in advance and keep them the same size.

Unlimited growth can be problematic if the file grows until you run out of disk space. In that case, no other database file on that disk will be able to grow and you could end up with multiple databases in trouble instead of just one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.