3

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.

3 Answers 3

3

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.

5
  • renniej - Have a look at the following two links. Autogrowth can lead to significant delays if you don't have instant file initialization enabled, and even if you do it will lead to physical disk fragmentation. As a best practice, you need to disable autogrowth. blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/… sql-server-performance.com/Community/forums/p/24508/137168.aspx Jun 25, 2009 at 13:15
  • I have to agree with Renniej, the biggest problem I've see with autogrowth is when it's set too small so it happens a few times in a row. That can be a sizeable perfomance hit. File fragmentation is only a problem on DAS. SAN storage won't be affected.
    – Jim B
    Jun 25, 2009 at 14:11
  • If your database's recovery model is full then you should schedule regular transaction log backups rather than trying to shrink the log. Jun 25, 2009 at 14:40
  • Re SuperCoolMoss' comment, you need to do a full or log backup first to truncate the log (or backup log with truncate only if you absolutely must). This truncates the log but won't shrink the .ldf file. You need the dbcc shrinkfile for that. NB dbcc shrinkfile is unlikely to do much unless you truncate the log first. Jun 25, 2009 at 16:23
  • Thanks for the great answer - I increased the size of the file, and taken your advice for the autogrow to be increased. Seems to be working great.
    – aSkywalker
    Jul 4, 2009 at 4:25
6

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!

4
  • Paul, I ran the script from your blog and sure enough 98% fragmentation. However a simple reindex reduced the fragmentation to zero again. Is there some longer term harm that a reindex does not cure? Jun 25, 2009 at 16:55
  • Where is this script you speak of?
    – Jeff Costa
    Jun 29, 2009 at 16:05
  • @John - yes - but the reindex grows the database again to build the new copy of the index, defeating the whole purpose of the shrink. Even if you run DBCC INDEXDEFRAG/ALTER INDEX ... REORGANIZE, it will generate a ton more transaction log to undo the effects of the shrink. @Jeff In the first blog post referenced above, there's a link to a blog post on shrink. Check it out. Jun 29, 2009 at 17:31
  • I'm with Paul. Always have this on, manage your space, and grow it appropriately. Do not shrink unless it's an emergency. Keep enough free space in to handle regular needs, including reindexing. Jul 14, 2009 at 22:03
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .