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

1 Answer 1


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 = 1 on the instance to get an idea of any problems that might be hiding under the surface.

  • ok, my 'accidental' part shows, i don't have right to run some parts of sp_blitz :-) i'll create an agent job to run sp_Blitz, the agent has the rights Jan 27, 2022 at 12:09
  • If you don’t have access to run sp_Blitz, you’re going to have a hard time maintaining the server. Is there someone still around with sysadmin permissions? If you’re running it from a job as a workaround, you’ll need to specify the results go to a table. Jan 27, 2022 at 12:36
  • yes, i have done that (saving in a table) nothing alarming shows up i'm in the process as to get better privileges thanks for your suggestions Jan 28, 2022 at 13:50

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.