While I mostly get questions from my client during Comprehensive Database Performance Health Check, recently I got questions from my friend who usually works in the legacy system. His question was about their database Logs, which were Full and they Can’t Switch Recovery Models. Let us discuss this problem today.
Can’t Switch Recovery Models
After his email, we quickly got on the call and I found out that he was using the database SQL Server 2000. I felt that he was using the earlier version of SQL Server, which is out of support and not many people nowadays are using it. They had a log file on the disk and now the disk was 100% full by the log file. The recovery model of the database was also a full recovery model.
Now the issue was they were not able to change the recovery model from Full to Simple to clear the log file. The reason for this is very simple and straightforward. In SQL Server 2005 and earlier versions of SQL Server, changing the recovery model was the logged operation. This means you need a space in your log file to change the recovery model. This put them into a catch 22 situation. As there is no space, they want to change the recovery model but to change the recovery model they need space. – Catch 22!
Finally, my friend was able to remove some other data from the disk and was able to change the recovery model. There is no real solution to this situation.
SQL Server 2008 and Onwards
Thankfully, SQL Server 2008 and onwards when we change the recovery model it is not logged operation. This means even though your disk is 100% full and you want to change your recovery model, you can successfully change them. I recommend all of my clients upgrade to the latest versions of SQL Server.
If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference: Pinal Dave (http://blog.SQLAuthority.com)
