I’ve gotten a transaction log full error but when I look it’s not full??

2

April 16, 2018 by Kenneth Fisher

Every now and again I’ll get an error telling me a transaction log is full. This can be for any number of reasons (make sure you are taking log backups on your full recovery databases) but many of my co-workers (DBAs and others) get really confused when they check and low and behold the log is not only not full, but it’s empty. Skipping over the easy case of a log backup between the error and when they checked, what’s going on?

The easiest way to look at this is if there is a single process running. It’s a long one, with a big transaction. That big transaction fills up the transaction log and the transaction fails with a log out of space error. Now the whole purpose of the transaction log kicks in and the transaction rolls back. Once the transaction has rolled back that part of the transaction log can now be re-used. And walla! (For those that don’t know walla is Texan for voila) The transaction log shows as empty again.

The same process holds if multiple transactions are running at the same time, it just gets more complicated. In fact, this whole thing is a bit of a simplification but the logic holds. FULL or SIMPLE recovery will have an effect but again, the logic holds regardless.

2 thoughts on “I’ve gotten a transaction log full error but when I look it’s not full??

  1. ddecasse says:

    are they referring to some of the log errors generated when replication gets behind?

    • I don’t think so. In that case replication doesn’t just end like a transaction. It’s going to sit there and wait until the replication is killed. At that point, yes, I believe the log would empty out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,176 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: