SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...