I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.

The DB files auto growth is set to 5MB and I would like to try a different approach, so I am looking for suggestions and or comments.

There is a tuning task that runs every-week on Sunday night at 1:30 AM. The task will:

  • Check Database Integrity
  • Shrink the Log File – (This is ok because logging mode is Simple)
  • Shrink Database
  • Reorganize Index
  • Rebuild Index
  • Update Statistics
  • Clean Up History

I would like to add two more steps to the weekly tuning plan:

  1. Grow the database file by 500 MB if the used space reaches a certain threshold or total size.
  2. Grow the log file by 250 MB (after the shrink) if the used space reaches a certain threshold of total size.

By placing the growth burden in offline hours, I hope to gain performance by reducing the number of auto-growth events during heavy loads.

I have two questions relating to auto growing files.

  • The best place to put the file grow steps would be prior to the current steps or after?
  • If I use the ALTER DATABASE|MODIFY FILE to grow the file then how can I determine if SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?
  • 2
    Your DB should have enough size that it never grows. None the less, make sure Instant File Initialization is enabled. Commented Aug 28, 2012 at 18:36
  • 3
    @Remus while ideal, are you saying that you've pre-sized every database perfectly throughout your career? There is always going to be some guess-work involved and adjustments to be made. I will agree that growth should be controlled and not just left to do so 7 times a day. Commented Aug 28, 2012 at 18:39
  • 3
    @AaronBertrand: I favor simplistic, hyperbole advice. In time I learned it sticks better. Most users can't handle the 'it depends' and those that do can figure out on their own that there are shades of gray in between black and white... Commented Aug 28, 2012 at 18:47
  • 3
    @DanAndrews: over-allocation can have effects 'downstream'. Think a dev trying to restore the DB on his machine only to discover it needs two new 1TB drives for 1Gb of data... Commented Aug 28, 2012 at 18:54
  • 2
    I'm only playing the devil's advocate. However, HDs are cheap. Lost time in production for reconfiguration and performance loss is expensive. Commented Aug 28, 2012 at 19:19

2 Answers 2


You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.

Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.

If recovery model is simple, there is no way on earth you should need to grow your log file by 250 GB. The used space in the file will clean itself out automatically over time, unless you started a transaction a month ago and have no intentions of ever committing it or rolling it back.

So my advice would be:

Auto-grow the data file manually during a quiet period to a size that will accommodate several months of growth. What are you saving it for in the meantime?

Set the auto-growth increment for the data file to something relatively small (so that it doesn't interrupt users when it does happen), and alert on this event (you can catch it in the default trace, for example, or through extended events). This can tell you that you are hitting the high point you estimated and it is time to grow manually again. At this point you will want to keep this manual in case you want to add a new file / filegroup on a different drive to accommodate the space, since eventually you will fill the current drive.

Auto-grow the log file to, say, twice the largest it's ever been. It shouldn't auto-grow further unless there is some abnormal transaction holding things up. You should monitor for this event as well, so that you know about them.

  • Thanks for the input...I will ues your advice for growing the log file and monitor it for a while. I incorrectly used GB for MB in the autgrow size. I agree and don't think the Shrink Database is doing what it was intended to do. At the end of the year the DB reaches 15GB, at the time the job was created we were running out of room for backups.
    – ltn
    Commented Aug 28, 2012 at 18:22
  • +1 for should be called auto-fragment :-) Have you launched a Connect issue for that already? :-)
    – marc_s
    Commented Aug 29, 2012 at 7:30

Auto growth is something that you should try to avoid if possible. The issue is you have no control over when the growth can happen and your system can take a serious hit while it does so.

Set your file size to something sensible for a month or so and monitor your growth rate from there work out how much space you estimate for X amount of time and set your size to that + a margin of error.

I've set up a simple monitoring job that will alert me when the file size hits a predefined maximum before auto growth. You could use something like this:

SELECT instance_name,
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
       into ##Logsize
   FROM sys.dm_os_performance_counters
   WHERE counter_name IN
       'Data File(s) Size (KB)',
       'Log File(s) Size (KB)',
       'Log File(s) Used Size (KB)',
       'Percent Log Used'
     AND instance_name = 'database your interested in' 
) AS Src
   FOR counter_name IN
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
) AS pvt 
declare @logsize int
Select @logsize = [Percent Log Used] from ##Logsize

If @logsize > the maximum percent you want the log to fill too i.e 90
        --Do your thing here

Drop table ##Logsize

This could of course be scheduled as a job.

  • I remove the "AND instance_name = 'database your interested in" so it returns all databases. Then use a count where the log size is a above a threshold in the IF statement. If the count is higher than 1 I do a sp_send_dbmail with a select name statement from the temp table to email the names of the logs above the limit. Commented Nov 24, 2012 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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