1

A few minutes ago I ran into a strange situation with my prod database. I have db X with simple recovery model. SCOM gave me an alert that the log disk is running out of space. I then checked the disk and saw that db X's log file is 11 Gb even though the data file is 300 Mb and the recovery model is simple.

Then I checked DBCC OPENTRAN but there were no open transactions, neither on the database nor on the server.

Then I tried to shrink the log file, but it said that there was no empty space. I checked the log_reuse_wait_type column and it was LOG_BACKUP. I know that, by definition, it should be impossible to see that wait type for a database using the SIMPLE recovery model, but there it was. I double checked all the steps that I mentioned above and nothing changed. Then I changed the recovery model of db X to FULL and set it back to SIMPLE. After that log_reuse_wait_type reported as NOTHING.

In the end I solved the problem but still wonder why it happened. Has anyone encountered a similar problem?

4

Are you running SQL Server 2012? Is your model database in SIMPLE recovery model? Are you a bit behind with your patching?

This appears to have been the issue for a previous ask of this question, at SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

Fixed in KB2830400 which forms part of SQL Server 2012 CU7 / SQL Server 2012 SP1 CU 4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.