Configuring Database Data And Log Files

One of the things that Database Administrators typically do is configure the Data and Log file growth settings for the databases on their servers. While these configurations vary by server and database (and by DBA!), I have outlined below a few of my personal preferences that I like to stick to.

Never Use The Defaults

While “never” may be a strong word, and I’m sure that there are cases with smaller databases that it’s perfectly fine, I am typically going to modify the default growth settings on the Data and Log files of my databases. The reasons for this vary, and the following scenarios are some examples of when I’ve needed to change Data or Log file settings:

Configuring Growth Increments

The default data and log file growth increments of 1MB and 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} are just stupid. I realize that Microsoft needed to put a generic default here, but come on, this is SQL Server, not Access. These defaults are somewhat understandable for SQL Server 2005, but not all the way up to 2014! (Prior to SQL Server 2005 the defaults were Data files 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} and Log files 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}) Even with SQL Server 2016, where the default auto-growth is 64MB for data and log files, I prefer to set mine higher. I typically won’t have an auto-growth set less than 512MB, but that’s just my personal preference. In actuality, I try to avoid auto-growths altogether. The reason for this is that auto-growths can be expensive. Each time an auto-growth event is performed SQL Server has to hold up database processing until the auto-growth is finished. So instead I’ll increase my data file size so that there’s plenty of free space, and then monitor it so that when it gets close to filling up the data files I can manually grow them during a maintenance window. I just have the defaults in place in case I’m not around when the database needs to grow.

For more information around auto-growth events, Redgate has a really good article. It gives some good information about how auto-growths can cause physical fragmentation in your data and log files. And if you want the details about how log file auto-growths can cause problems with writing to your database, see their follow up article on Transaction Log Fragmentation.

Limiting The Maximum Size

DBAs don’t like surprises. And one of the surprises they like the least is getting a 3AM phone call where something has blown up a log file and filled up a hard drive. And while the typical recommendation around log files is to allow them to grow to the size needed, I also prefer to put safeguards in place to keep them from filling up a drive.

For example, if I have 4 log files sitting on a 100GB hard rive, I may set a maximum size on each log to make sure the drive doesn’t fill up. Or, maybe I have a log file that I know does a lot of critical processing and I want to make sure it has room for growth if it needs it. I can limit the size of the other logs to ensure they don’t take up all of the drive space.

So if I wanted to set the maximum size of a log file to 12GB, I would run the following:

(One thing to point out: If you notice, I specified the size in GB instead of MB in that last statement. SQL Server doesn’t care, you can use either one and it will figure it out. Pretty cool, huh?)

Pre-sizing The TempDB Data And Log Files

TempDB is one of the hardest working databases on your server, and it sees a lot of action. When TempDB is in the middle of working on something, you don’t want it to have to stop for auto-growth events, this is why DBAs will typically create multiple data files for TempDB, put them on their own drive, and already have them grown to a size large enough for TempDB to work without having to worry about growing the files. In fact, one of the newer recommendations in the last few years (which I like) is to create 8 equally sized data files and size the files to take up the whole drive, then turn auto-growth off. That way it has an entire drive to use for its processing and doesn’t have to worry about growing.

For example, if I wanted to set the data and log file size to 10GB, and turn off auto-growth:

Or, another way of accomplishing this would be to set the maximum size to what your current size is.

There are a few other File Options as well, but these are the ones I typically use. As always, don’t just take this and blindly implement it, be sure to test changes such as these to see if they are useful in your environment.