Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
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 –  Aaron Alton Jun 25 '09 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 '09 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. –  SuperCoolMoss Jun 25 '09 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. –  John Rennie Jun 25 '09 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 '09 at 4:25

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!

share|improve this answer
    
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? –  John Rennie Jun 25 '09 at 16:55
    
Where is this script you speak of? –  Jeff Costa Jun 29 '09 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. –  Paul Randal Jun 29 '09 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. –  Steve Jones Jul 14 '09 at 22:03

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.