Kalen Delaney : transaction log

Geek City: What Triggered This Post?

Kalen Delaney — Tue, 01 Jan 2013 00:00:00 GMT

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that...(read more)

Follow-up Answers for my Australia Classes

Kalen Delaney — Wed, 04 Apr 2012 22:56:00 GMT

I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney, which were organized by WardyIT . It was a great visit and there were 24 terrific students! As is sometimes (perhaps often?) the case, there were questions...(read more)

Geek City: What gets logged for SELECT INTO operations?

Kalen Delaney — Tue, 15 Mar 2011 19:41:00 GMT

Last week, I wrote about logging for index rebuild operations. I wanted to publish the result of that testing as soon as I could, because that dealt with a specific question I was trying to answer. However, I actually started out my testing by looking...(read more)

Geek City: What gets logged for index rebuild operations?

Kalen Delaney — Tue, 08 Mar 2011 18:54:00 GMT

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait...(read more)

Geek City: Exploring the Transaction Log Structure

Kalen Delaney — Tue, 22 Dec 2009 00:48:00 GMT

As I mentioned last October, my PASS preconference seminar was a whole day about the transaction log . I told the attendees all kinds of things about the structure of the log, and told them about one of the best tools for exploring the structure: the...(read more)

Geek City: 24 hours of SQL Server!

Kalen Delaney — Fri, 14 Aug 2009 01:13:00 GMT

I know, my Geek City posts are usually really technical, but I figured it’s only Geeks who are going to want a solid 24 hours of SQL Server presentations… so this post is dedicated to all the SQL Server Geeks out there! I will be presenting as part of...(read more)

Geek City: When is FULL Recovery not Really FULL Recovery

Kalen Delaney — Sun, 30 Nov 2008 23:30:00 GMT

You may think that having a database set to FULL Recovery Model means that your database can be fully recovered. The key word in that previous sentence is 'can'. It is possible to fully recovery a database in FULL recovery, or to restore it to an arbitrary point in time, or up to the point of a system failure, IF you have been a good DBA and you have been taking your regular backups.

FULL Recovery means that all your changes to the database are fully logged, and you might think that also means that the log records stay in the log until the log records are safely stored away in a log backup.

But that is not completely correct. You database can be in FULL Recovery model, but also in a state called auto-truncate mode.

Most people are familiar with auto-truncation in SIMPLE Recovery, and of course that is one way to get a database into auto-truncate mode.

If your database is in auto-truncate mode, the log will be truncated every time a CHECKPOINT is run against the database. Checkpoints happen at very frequent intervals, which by default is usually about every minute. Truncation means that all log records in inactive parts of the log are marked as available to be overwritten if the log space is needed. Truncation does not physically shrink the log file, but it can keep it from physically growing.

So how do you get in auto-truncate mode? There are three ways:

1. As a mentioned above, if you put your database into SIMPLE Recovery, it will be go into auto-truncate mode.

2. If you backup the log without saving the log records, using BACKUP LOG ... WITH TRUNCATE_ONLY, no more log backups can be made, and the database is now in auto-truncate mode.

3. If you have never taken a FULL backup of your database, you can never take log backups, so there is no point in keep log records available. Your database is in auto-truncate mode until the first FULL backup of the database is made.

3a. If you have never taken a FULL backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery. (Thanks to Hugo)

You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log.

So how can you tell if your database is in auto-truncate mode? The recovery_model_desc column in sys.databases only tells you what recovery model you have set, not what behavior your database is using. To see whether a database is in auto-truncate mode, you can query a undocumented system view called sys.database_recovery_status and look at a column called last_log_backup_lsn. If that value is NULL, it means the database is not maintaining a sequence of log backups and it is in auto-truncate mode:

SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status

Here's a whole script you can use to test this for yourself:

-- Check for auto-truncate mode
-- If last_log_backup_lsn is NULL, log is in auto-truncate mode
CREATE DATABASE FOO
GO
-- Check status after first creating db
SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP DATABASE foo TO disk = 'C:\foo.bak'
GO
-- Check status after backing up db
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP LOG foo WITH TRUNCATE_ONLY
GO
-- Check status after truncating log
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
DROP DATABASE foo
GO

Note that there is a change in behavior in SQL Server 2008. The BACKUP LOG ... WITH TRUNCATE_ONLY option is no longer available. If you want to force the log to be truncated (and enter auto-truncate mode) the recommended method is to change your recovery model to SIMPLE.

Have fun!

~Kalen

Geek City: Reading the Transaction Log

Kalen Delaney — Sat, 23 Aug 2008 21:07:00 GMT

Sorry, I'm not actually going to tell you how to read the log. I'm just going to talk about it... and whether it's a good thing to be able to do or not, or whether it's an absolutely crucial feature that MS needs to provide for us immediately, in a hotfix, if not sooner. Forget about fixing bugs, I want to read the log because I forgot to set up a trace beforehand....

You may have noticed that my blogging frequency has fallen off. One or two of you also noticed that I am no longer writing a regular article each month for SQL Server Magazine. Those two facts are related. I have cut back on non-essential activities to try to get my next book out as soon as possible. It looks like I might even finish in time to get the book out on the shelves by early next year. Stay tuned...

Since I couldn't bear to not do anything for SQL Server Magazine, I started writing the commentary in the weekly email newsletter. Actually, I do it every week but the fourth week of the month. If you like, you can sign up for this free newsletter here.

My commentary last Thursday seemed to have rattled some cages. Before I even woke up Thursday morning, there were already two comments on the site, and someone sent me a personal email about what I wrote. By now, there are quite a few more comments. I basically wrote about the need for a log reader tool. It wasn't deeply technical; it's just a commentary after all. You can read it here:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=100076

But boy, did people get upset. They called me bad names... well, if 'mediocre' can be considered a bad name...

So I responded as follows:

Wow... I have never gotten so many comments so quickly about one of my articles. I must really have touched a nerve here!
There is a difference between the actual data rows referenced by the log, and the log format. It's the log format, and giving people full details about what is in the log, that is propriatary information. There is nothing specifically bad about giving people that information. However, calling me names because I don't stand up on a soapbox and DEMAND that MS add this functionality seems a little extreme. There are plenty of other things MS could do with the product and providing a log reader tool is way down on the list.
Yes I realize it is important to some people, but there are many other ways to get this information through tracing etc. If the developer resources are limited at MS, I would much prefer they spend their time on more important stuff. MS knows it's important that people have this information, that's why they added a great deal of additional tracing capabilities in SQL Server 2008.
Also, keep in mind that a log reader tool wouldn't help you debug problems with logic, or with bad reports due to faulty SELECTs. If your WHERE clause was written badly, a log reader tool could tell you which rows were affected, but not WHY. You'd need a tracing tool for that. Vogelm's comment that a log reader tool would help troubleshoot bad queries from 3rd party apps is not true; you need to see the statements for that, not just the affected data.
I do appreciate kbreneman's comment that the real problem is one of perception. MS should make clear that the transaction log is not an audit tool; if you want auditing, you need to set it up on your own, because you're the only one who knows what's important for you to capture.

(The only way to respond to comments is to write a comment of my own, and then the form insists that I rate the article I am responding to. I always feel a bit weird having to rate my own articles.)

Since I wrote the article, I have found out that Lumigent does have a log reader tool for SQL Server 2005, but I have heard less than stellar reviews about its ability to capture some of the more interesting datatype activities that are now possible in SQL Server 2005. And their website still doesn't list any version numbers.

I can't stop thinking about this, so I thought I would open up the issue to a wider audience.

How important do you think it is that Microsoft provide a log reader tool for us?

Thanks!

~Kalen