Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a database that will growth in 1 month time the autogrowth was set to 500MB but as I monitored it, the consumption is 1GB+ per day. Should I wait for it to auto grow or is it better to set the initial size to much bigger size? Or set the auto growth to much higher size?
Another thing is the timing of expanding the size, is it ok to do it online while there is a production will it have a minimal impact to our production or should I schedule a downtime?
Thanks for the response.
If you know it's going to grow 1GB per day, then yes, manually expand the file now (well, add many files and expand them all) to support as much future growth as you can afford.
Every growth event has a cost. Even with instant file initialization enabled, transactions have to wait for it to complete. If you aren't using IFI or you have extremely slow storage, these surprise events are definitely not a good thing.
Growing the file(s) in advance now has an obvious cost to disk space, but it's not like you could have used that space for anything else. This does not have a cost to backups, since empty pages/extents are not backed up.
Whether it will impact production will depend almost wholly on whether you have instant file initialization enabled. You are using an ancient, unsupported version of SQL Server, otherwise you could check this in a simple DMV or in the errorlog. There is an elaborate way to check on older versions, explained by Paul Randal here:
