I was recently asked to describe (to a non-technical audience) what SQL Transaction Logs were. While this is bread-and-butter stuff to think about, it can sometimes be difficult to articulate – especially when asked off the cuff with no context. This prompted me to write up a post so that it might help someone else or give me something to refer to in the future. Now would be a good time to grab a brew, as you may be here a little while…
Transaction Logs
These are circular files that are divided up in a number of virtual log files (VLFs). The SQL engine sequentially writes “transactions” that are performed on the database as entries to these VLFs, when one VLF is full, SQL moves to the next. When SQL reaches the end of the VLF it will go back to the start, hence the term “circular”. This effectively means a VLF can be in one of two states; active or inactive.
Active VLFs are, unsurprisingly, needed by the SQL engine as they may be part of “active” transactions and may also be required by mirroring, replication, or backup jobs. They are also associated with transactions that have not yet been committed to the SQL data file.
Inactive VLFs contain logs that are not required by the SQL engine. Essentially, the logs have been committed to the SQL data file. If the database is set to Full or Bulk-Logged recovery modes, then the inactive VLF logs are backed up, otherwise they are simply discarded. You know when us SharePoint Consultants beat the drum of Full recovery mode in Production environments? Well now you are starting to get a picture of why 🙂
The process of making a VLF “inactive” is more commonly referred to as log truncation, although not strictly a correct use of the word. Actually, nothing is shortened (or truncated) by making a VLF inactive, it merely means the space can be reused by the SQL engine. You’ve probably seen the “… WITH TRUNCATE ONLY” statement suffixed to the end of a BACKUP query, right? Please don’t confuse that query with meaning your log records are discarded and the backup chain broken, because it isn’t.
Before a VLF can be reused by the SQL engine, it must be inactive. If all VLFs are active and the current VLF being written to has filled up, then SQL has no option but to attempt to grow the transaction log. I said “attempt”, yes? Yes. Should the transaction log fail to grow then you will start to receive error 9002, which is bad news as data modifications will now fail.
Error 9002
So you’re running transaction log backups but you are receiving the dreaded 9002 error:
Error: 9002, Severity: 17, State: 2.
The transaction log for database 'YourDatabaseName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Of course, missing or failed backups are one cause (check your maintenance plans first!), but what should you check next? The clue is actually in the error; sys.databases. There is a column called “log_reuse_wait_desc” which we should look at first, using the following SQL query:
DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'YourDatabaseName'
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName
Whatever value is returned for the “log_reuse_wait_desc” column is the reason the space cannot be reused. However, should there be multiple reasons, the above SQL query will only return one, so I would recommend running it two or three times and ensure the results are the same… and of course, again after you believe the issue is resolved.
A little further reading for you on this rather nice MSDN article about the definitions of the descriptions found in the “log_reuse_wait_desc” column.
Thanks for reading and have a great day!
