1

I have a Log backup job running every hour via SQL Agent. This morning it failed at 3:00 AM with this error:

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.

But it ran fine at 2:00 AM. So sometime between 2 and 3, it went from FULL to SIMPLE. My response was:

  1. Change it back to FULL recovery
  2. Run a FULL DATABASE BACKUP
  3. Run the Log Backup job to make sure it would still work. All good, no data loss.

So I got to investigating the Default Trace for events about possibly WHO might have made the change between 2 and 3. I first identified the event on Step #1 above so I knew what types of events I was looking for. Unfortunately, there is no event to say, "The recovery model changed from x to y". All it has is "Object: Altered" for my login for the database in question, with rows for BEGIN and COMMIT.

Here's the issue: I found NOTHING in the default trace in the time span to indicate that anyone made a change to this database.

Question: How could this change have occurred and it NOT get recorded in the Default Trace? It certainly recorded the fact that I changed it back. Why not the first change?

Any help would be appreciated.

Improve this question
2
  • 1
    Todd this is obvious but still checking any chance your default trace duration rolled over and passed the time when the database recovery model was changed?
    – SqlWorldWide
    Commented Jan 24, 2021 at 21:19
  • Yes, to expound on my findings. This was an Azure SQL VM and as such the Automated Patching window took advantage and patched the SQL instance. Looking at the SQL logs, I think the default trace was stopped along the way somewhere, which is why this was not captured in that tool. But I DID see the change explicitly in the SQL Logs. Which makes me wonder if there is a bug in the SQL patching that does this and then NOT return msdb back to FULL as it found it.
    – Todd Chittenden
    Commented Jan 25, 2021 at 22:24

1 Answer 1

Reset to default
1

Answering my own post as I found additional information:

The trace log had no indication of Object: Altered for that database, which happened to be msdb (might be important here). In the SQL logs, I found indications that someone had applied updates, and after the first instance start-up, there was indication that msdb had an upgrade script applied then setting of SIMPLE recovery, along with creation of several synonyms.

Improve this answer

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