Home / server / Questions / 31499
Accepted
aSkywalker
Asked: 2009-06-26 03:54:28 +0800 CST

Can I change a live SQL Database from Autogrow safely?

  • 772

I have a live SQL 2005 database, and am in the very unfortunate position of being the involuntary DBA.

The database has an MDF file that is 1.4 GB, and an LDF with 2.2 GB. The Autogrowth is set to growth unrestricted by 10%. I have lots of disk space - so thought it would be better to set the initial size to be considerably bigger. It is growing quickly - doubling in size in about the last 6 months.

Can I just pick a a high number (since I have plenty of space), and simply change the Initial Size (perhaps to 4,000 or something) - and then set it as a weekly check to make sure we are not within some percent of this number?

Thanks.

sql-server ssms sql

3 Answers

  1. How timely - I just wrote a log blog post about exactly this issue yesterday - check it out at Importance of data file size management. Summary:

    • size the data files initially to account for current size plus at least a year's growth, if possible
    • turn on instant file initialization if you can (note: this only affects data files, log files always have to be zeroed)
    • ALWAYS have autogrow turned on - I totally and unreservedly disagree with anyone who says the opposite. It should always be on for emergencies when your monitoring fails (unless the SCOM bug is preventing you having it on - see the article for explanation)
    • set auto-grow to a fixed, appropriate size
    • Don't rely on auto-grow. Monitor file usage and manually grow. Monitor for auto-grows happening in emergencies
    • don't ever, ever shrink if you can avoid it. My blog posts has a script that shows you why.
    • your log file seems too large. Checkout this other blog post Importance of proper transaction log size management for some hints and tips.

    Hope this helps!

    • 6
  2. Best Answer

    Yes. You can increase the mdf initial size and SQL Server will grow the file to that size. It's quite safe to do this on a live database, though choose a quiet time! You should find the size increase is very quick. I just grew a test database from 128MB to 4GB and it took 2 seconds.

    An initial size of 4GB seems reasonable given the current size of the database. If you have lots of disk space why not set the growth to something high, e.g. 2GB or even 4GB? Growing the database in large increments reduces the physical fragmentation of the mdf file.

    You don't need a weekly check as SQL Server will just keep growing the file. Just make sure you don't run out of disk space.

    JR

    PS I've just seen Aaron's reply. I differ from him in that I have no problem with autogrowth of the database. However you want to set the autogrowth parameters to avoid lots of small increases. 10% is the default, and I think that's far too small for most databases.

    PPS that log size looks a bit big. Is the database set to "Full logging", and if so are you sure the database is being backed up? If the log file size gets out of hand you can use "dbcc shrinkfile" to reduce it. See the Books Online for details.

    • 3

  3. aSkywalker, use The Force or alternatively take a look at Paul Randal's article for 'involuntary DBAs' here:

    http://207.46.16.252/en-us/magazine/2008.08.database.aspx

    • 1