leyton.org

I get a number of queries and e-mails hitting the website on all manner of topics, one of the most common items being simple questions regarding management of transaction logs in Microsoft SQL Server 2000. I thought it might be useful therefore to put together a few posts on simple technology issues that might be of use for visitors. There are other resources out on the internet of course, so these are just some quick pointers to guide you on your way.

This first post is on the simple task of truncating down the SQL Server transaction log.

The first SQL Server system I encountered some years ago hadn’t been set up properly, and was eating the available disk space, leading to all sorts of problems.The first thing to be aware of is that a transaction log is associated with a database, and not the server itself. A SQL Server will have a number of databases, each containing various sets of information. For each database, whether it’s master, model, msdb, tempdb (the system databases), or – of course – your own database – there are transaction logs for each. Whilst the system databases will most likely be taken care of during installation, any that are created subsequently may not.

Do be aware that a transaction log is important for standby and replication purposes. Ensure you know the full use to which a transaction log is being put before leaping in and truncating it. There is no going back. Truncating it will break log shipping and any replication you might have in place. If there is a contingency system which is kept in sync, you probably have log shipping!

So what are the indicators to look for when the transaction log is full? One, of course, is an error message that you might see in the client session saying a transaction has been suspended, or failed and been rolled back. Another is that you just notice the transaction log is huge: Hundreds of megabytes or gigabytes of allocated space when the system is quiet, perhaps. Of course the Server and client error logs are the first place to look.

So assuming you know you need to truncate the transaction log, there are three approaches to achieve this end:

• In Enterprise manager, set the recovery model of the database to “Simple”. A Simple recovery model ensures that when every checkpoint occurs the transaction log is truncated down. As transactions occur, the log fills up, and it’s only when a transaction is either committed or rolled back that the space can be freed. This is directly analogous to the Sybase setting “trunc. log on chkpt.” (Truncate log on checkpoint), and can be achieved through use of the ’sp_dboption’ stored procedure.

• Run the direct SQL operation “dump tran dbname with truncate_only” in Query Analyser. This truncates the transaction log. In some situations you may need to change truncate_only to no_log, which is slightly more restricted in what it does.

• If a database is, for some reason, being created and dropped frequently, or that you want to prevent the problem occurring for new databases, change the ‘model’ database properties to “Simple”. The model database is just that, a model by which other databases are built (including tempdb). Any new database therefore “inherits” the properties, sizes and contents of the model database. Setting it to a simple recovery model will ensure new databases are created with an automatically truncating transaction log.

Of course, it’s also possible to simply increase the quota of space allocated to a transaction log in the properties. I generally set an upper limit so that an entire server (especially if disk space is limited) isn’t knocked out by one errant database or operation (this is particularly true of tempdb when running poorly written queries!). Ensure they’re set to sensible levels, and appropriate for the application to which the database is being put.

There are some caveats, one of the most obvious of which is that if there’s an open and long-running transaction, you won’t be able to truncate the log as such. This is because the system always needs to be able to recover (think ACID) a database. In this scenario, it’s better to look for this transaction (Enterprise manager’s process/lock viewer or use sp_who2), and ask the user to cancel it, or kill it (”kill spid” in Query analyser). The transaction will be rolled back, and the transaction log potentially cut down to size.

Also, truncating a transaction log may not necessarily recovery the space that has been allocated on the disk. Here you need to shrink the underlying file (Enterprise Manager’s Taskpad is the easiest way in my experience) to recover the space on disk. But you’re better off ensuring the upper limit on the transaction log size is specified so it never grows beyond what you’re happy with in the first place.

The usual disclaimer applies: None of what’s contained here is a substitute for a good understanding of the technology in question. It’s probably worth your while to go and buy a decent technical reference book on SQL Server, enrolling yourself on a course, or hiring people to help out (something I might be able to help with!). However, that’s not always practical or appropriate, and hunting about on the Internet for an answer is sometimes the only option. With that in mind, the answers here are hopefully self-contained, but should be followed with caution: Every system is different, and what is contained here may not be the right solution for a particular set of circumstances. So, in a nutshell: Use at your own risk.

This entry was posted on Wednesday, June 8th, 2005 at 11:03 am and is filed under Databases, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.