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.
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.