I was recently working with a client when I noticed a job about shrinking transaction logs. I asked the client about the job and found out that they had a weekly scheduled processes to switch the recovery model of a database from full to simple, shrink the transaction log, and then switch back to the full recovery model. They also were not using native SQL Server backups, they were just relying on daily VM level backups.
I immediately explained how vulnerable the client was to potential data loss and how a proper backup back up process would allow them to meet their recovery SLAs. By switching recovery models and not having proper transaction log level backups, they did not have any point in time recoverability. I also shared how a database in full recovery without transaction log backups would cause the transaction log to continue to grow, by taking regular log level backups, the transaction log is able to clear empty VLFs and reuse that portion of the log.
The client was very receptive and we quickly implemented a backup plan that would meet their recovery needs.
Switching recovery models have their purpose, but automating a process like this just to save space screams of a bigger issue. If your transaction logs are growing to an unruly size, there is a reason. Either you have long open transactions that are preventing VLFs from clearing, not taking log backups frequently enough to clear the log, index maintenance may need to be adjusted, or a number of other reasons. For most organizations, allocating the space that the logs need for normal business processes and maintenance is sufficient and an understood cost of doing business.
Every DBA who is responsible for backup and restores should be familiar with your restore process and validate that what you think you can do in terms of restoring, can actually be done. The time to find out that you don’t have proper backups is NOT when you are restoring production. If you inherited a system recently, don’t trust that you are protected. Verify and validate that maintenance and backups are being performed to meet the expected SLAs.
I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101
One thought on “SQLskills SQL101: Recovery Models Matter”
Hi Tim,
Thank you for highlighting this, it is true, backup and particularly restore is the most important exercise an organization should have but most of the organization taking it for granted unless a disaster is hit and backup is not restorable.