Change datafiles size and growth



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2