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.