Managing SQL Server 2005 & 2008 Log Files

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
10:00
  -
  -
  -
  -
10:15
  -
  -
  -
  -
10:28

TODO: Sequence text against timeline




Hello and welcome to this SQL Server Video. My name is Michael Campbell, and in this how-to video we’re going to take a look at log file management tasks for SQL Server 2005 and 2008.
To do that, we’ll take a look at two key areas of focus:

First, we’ll look at specifying recovery model settings as they define how older, or inactive, portions of the log file are handled after each checkpoint. And this has a huge impact on the amount of space required by SQL Server when it comes to handling logging operations. Recovery models also have a huge impact on what kind of recovery options you have when responding to a disaster.

Then, we’ll turn our attention to managing log file sizing and growth options – which can be handled both from within SQL Server Enterprise Manager, or via T-SQL. And, as we do so, we’ll cover some best practices for governing log file growth and sizing needs.

Of course, since this is a how-to video, it’s assumed that you already have a decent understanding of the core concepts surrounding logging, backups, and recovery models. Therefore, if you’re not familiar with those concepts, you’ll want to check out the conceptual videos in this series that are listed on the screen.

So, let’s get started by looking at setting Recovery Model options for user databases using SQL Server Management Studio.
And the first thing to note about setting this option is that it’s done on a database by database basis. Since all new databases added to your server will taken on behaviors and settings defined by the model database, you can configure that to a particular setting if you’d like that as the default for all new databases – but this setting is really designed to be able to vary from one database to the other.
Therefore, after accessing the properties dialog for this database, we can then switch to the options page where we’re able to select which recovery model we want to use from a dropdown.

In terms of the options available, and what they mean, simple recovery means that you have very ‘simple’ options when it comes to recovery. In other words, SQL Server will still need to use the log to perform write-ahead logging operations that ensure dependable modifications and interactions with underlying data, but once checkpoints are issued older log entries are truncated – freeing up log space within the log file to record additional transactions.
On the other hand, when a checkpoint happens on a database using Full recovery, the inactive portions of the log file are left in place…. Forever … at least until a log file backup occurs at which point the inactive entries are copied, or backed-up, to another location – and then these older entries are then truncated from the log to make space for additional transactions.
With the Bulk-Logged recovery model, inactive portions of the log are managed just like they are with full recovery – meaning that you’ll want to make sure to back them up regularly. However, with the bulk-logged recovery model certain ‘bulk’ operations – such as the creation of INDEXES, modification of BLOB or text data, INSERT INTO and BULK INSERT operations – are ‘minimally logged’ meaning that they take up less space in the transaction log. To ‘minimally log’ these operations, SQL Server still logs the occurrence of the operation, but rather than recording every single change in minute detail, it just keeps track of which extents and pages were modified by the operation – and that information can be used to allow for full recoveries in the case of a disaster. Of course, because some operations are minimally logged, you do risk losing some control over trying to replay them, but you also benefit from improved performance and the need for less log space.
Under all three models however, the idea is that either the checkpoint process, or the combination of the checkpoint process and regular log-file backups, are removing the inactive portions of your log file such that SQL Server is able to keep logging transactions without allocating more log file space.
And, if those processes aren’t able to provide enough log file space, then SQL Server will either need to grow your log file or your database will come to a screeching halt. So, we’ll address managing growth options in a minute – after looking at how to set recovery model options using the T-SQL ALTER DATABASE statement.

To modify recovery options using T-SQL, you just need to specify which option you want and press F5 to execute your query. In this case, since the SSV database on this server is just a development, or testing, copy of the database, we’ll go ahead and set the recovery model to simple – as we don’t care so much about needing to recover data to a point in time or right up to the second of a crash should one occur.
Then, if we re-open the properties for the SSV database, we can see that our change has taken effect – as anticipated.
Now, while there’s no technical hindrance to changing your recovery model at will, you’ll want to take care when switching your recovery model back and forth like this as it’s possible to invalidate your log – which means that it becomes useless for disaster recovery purposes.
Likewise, if your database has been running in simple mode and you switch to full or bulk-logged recovery modes, your log files don’t, effectively, have a valid point of reference to start from and are really useless until you execute a full backup.
Therefore, when it comes to recovery models, the best thing to do is figure out which model best meets your needs and stick with it. And, if you do need to change it, it’s always a good idea to do a full backup right after any change – just as a precaution.

So, with that in mind, let’s go ahead and transition to looking at how to manage log file growth.
As you can see, log file sizes and growth options are managed from the properties dialog as well - and, it’s worth noting that the techniques for managing log file sizing and growth represents the same approach you’ll use when managing normal data files.
As a rule of thumb, it’s typically a good idea to try and make your log files 25% the size of the data files in your database. So, if you’ve got a 10 GB database, then a 2-3 GB log file would typically make sense. In some cases, this might be a bit large – but it’s generally much better to have more log file space than you need, as running of space will effectively kill activity on your database.
Of course, in some cases, you’ll also need more space in scenarios where there is a lot of write activity, or where transactions may keep resources tied up for fairly long periods of time.

And you can get a feel for how full your log file is by simply executing a standard report for your database from within SQL Server Enterprise Manager. Likewise, you can also use the DBCC SQLPERF command to look at log file sizes and percentages used.
Therefore, a key component to properly sizing log files is to keep an eye on activity (especially during peak usage periods) and make sure that your databases have a decent buffer in place to handle unanticipated spikes.
And, to achieve optimal performance, you’ll also want to allocate enough log space for your database to work through normal operations without the need to grow because the process of adding log file space does add a bit of performance overhead. Though, in SQL Server 2005 this performance hit can be slightly negated through the use of fast file initialization. Worst of all though, the need for this kind of file growth commonly occurs when a really large operation needs more log space. And, more often than not, these big operations tend to block other, smaller, operations until they’re done. So, stopping everything, even just for a few milliseconds, in cases like this isn’t ideal.
Likewise, if you’re constantly growing your log file by tiny increments, you run the risk of adding physical fragmentation into the mix.
So, using the GUI we can set both the base size for our log file, as well as specify auto-growth options. And, if we specify a larger size than we’re currently using, SQL Server will grow the log file once we click OK. It won’t, however, shrink the file to a smaller size if we select a lower value than what is currently allocated – so we’ll look at the process of shrinking the log file in another video.
We can also set growth options, either in megabytes or as a percentage of the current size. Personally, I don’t like the percentage option, so I tend to grow my log files in megabytes.
028 And one thing that needs to be said is that you shouldn’t be afraid to grow your log files in larger gulps. If you’ve got a 60 GB database, and you’re running a 15GB log file, don’t be afraid to grow that file by 500 or 2000 MBs – because if you do hit the need allocate more space, you’ll typically want that ‘hit’ to last for a while – instead of nickel-and-diming yourself over and over with piddly little 10MB spurts.
Finally, if you don’t set a maximum size for growth, SQL Server is happy to just keep growing the log file until it runs out of disk – assuming, of course, that it needs that space. So, on systems where multiple databases (or other applications) are competing for disk space, you’ll want to prioritize which databases can grow to what sizes, and enter those figures accordingly.
It’s also worth pointing out that once you approach the region of less than 25 to 20% free space on any disk it’s normal, and even anticipated, to start seeing performance problems due to disk saturation and queuing – so you’ll want to take that into consideration when accounting for max file sizes.

It’s also possible to manage these growth options using T-SQL… and you can find exact documentation and specifics on the type of syntax to use by looking at the ALTER DATABASE statement in books online.
Otherwise, as you can see from this sample query, changing database sizes or growth rates and maxes is pretty easy to handle via SQL. However, as mentioned earlier, you can’t use this functionality, to decrease the size of an existing log file. But, if you’re interested in that, make sure to check out this video’s companion video which focuses on shrinking SQL Server log files.

In conclusion, when it comes to managing log files there are only a few minor things that you need to watch out for – such as invalidating your log file when changing recovery models, and potentially adding performance overhead by allowing your log files to become too fragmented or letting it start out too small. So, with those issues in mind, thanks for joining us and make sure to check out other videos in this series to learn more.