Change datafiles size and growth
Alberto last edited by
I have 'inherited' a database as the accidental DBA on a SQLServer 2016 instance
It is 2TB in size
It has 9 datafiles, but 8 are set to no autogrowth, 1 grows by 500MB and is by now 1.6 TB, rest of the data is in the other 8 files
I would like to make it more even, so that not all activity is on the same file
Can i resize the ones that dont grow and make them grow evenly. or should i also set number 9 to nogrow and create 9 new files of same size and autogrowth ?
hope i made my problem understandable
There's nothing wrong with having only one data file with auto-growth enabled. After all, each database is initially created with only one data file, and many times, no additional data files are ever added.
If you're not seeing any contention issues with only the 9th data file having auto-growth enabled, leave it as is. You probably have much bigger problems than this to worry about if you just "inherited" this database. At most, I'd evaluate how often it needs to grow, and consider pre-sizing it to cover the additional growth it'll see in the next 12 months.
Go over to BrentOzar.com and download the First Responder Kit and run
sp_Blitz @CheckServerInfo = 1on the instance to get an idea of any problems that might be hiding under the surface.