i logged into a client's server today(which had log shipping for all of its db's), and found that someone changed all the databases recovery models to simple. so now i need to reconfigure the log shipping. my client says he didn't touch the server in the error log i can see it was changed. can it be some kind of a bug? the server wasn't restarted im lost... can the recovery model be changed by itself or by an application? thankserror log

  • 1
    The default trace will have this information unless the events rolled off. – Dan Guzman Jan 31 '18 at 12:35
  • 1
    I've actually seen certain SharePoint bolt-ons that do this at my last position. No clue why the application thought it needed to constantly reset the DB recovery mode. – Kris Gruttemeyer Jan 31 '18 at 12:38
  • i really hope its not going to happen again... it took me 2 work days to do their log shipping last time...ill ask if they use sharepoint. – sql_girl Jan 31 '18 at 12:39

Recovery model is changed by a specific ALTER DATABASE statement, and as far as I've seen, there's never been a bug that would change it.

Some common sources of that statement include:

  • Backup software - some 3rd party backup software will put the database in simple recovery model, especially virtualization backup tools that just do snapshot backups rather than log backups.
  • Data warehouse load scripts - I've seen ETL processes that would flip the database into simple recovery model to eliminate the need for transaction log backups. (Not saying that's a good idea, just saying that I've seen it.)
  • Log space reduction scripts - sometimes folks try to shrink their transaction logs, and run into difficulty, so they flip the database into simple and then flip it back.
  • yeah im just breaking my head here trying to find who did it.. – sql_girl Jan 31 '18 at 12:36
  • since they dont have a BI process there i think the first option makes the most sense thanks any way! – sql_girl Jan 31 '18 at 12:36

so yeah it turnes out my client's system admin thought he could save some space if he wont backup the transaction log and didnt think he should have asked anyone beforehand..... so no magic recovery model settings... thanks everyone

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.