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 ?
Sql-server – what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database
sql server
Related Question
- Sql-server – Sql Server – Best Practices for Growing Database Files
- Sql-server – Performance impact of Splitting Table across files
- SQL Server – How to Find Processes Causing Database Autogrowth
- SQL Server Filegroup Full – Could Not Allocate Space for Object
- Sql-server – The Filegroup PRIMARY for the database CRM running out of space
Best Answer
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.