I have seen a sql server database stuck in recovery state and lost a day worth of work in the past. This morning the last think I have in mind was writing about this. I am a developer and I don’t usual write about this kind of DBA work in my blog unless I a very good idea. But I dare to write about this today without having too much knowledge because I could not find something useful on the web and I manage to restore my database to a normal status with few simple steps.
The short story is I was writing a procedure that will archive millions of records this afternoon and I thought I finally come up with an optimal code and I decided to test it. I wasn’t too happy with the performance so I decided to stop the procedure and start to work on the optimization again.
Yap the database was at the middle of lot of transactions and just like that I put my database to In Recovery status. I was working on the development machine I have good backups but for second I thought if how much I would be upset on myself if I am a DBA and this happen to me on production environment.
I was hoping I will find something on some blog or MS books online and start rummaging the web. All I could found was more people asking questions like me than any good quick solution that I could restore my database to a normal mode and get back to work. Okay no help from the web and we are on our own and let us try to solve this.
Before we begin I repeat I am not experienced DBA and this post is only to share what worked for me in one instance and should not to be taken as a guideline to solve and unless you experiment enough it works please don’t try this in you production environment.
First I stopped SQL Server service then I moved the Log file to a different folder
Next I started the server and take the database offline
Finally I took the log file back to the original folder then right click on the database on SSMS and bring it back to online.
Just like that with few CPR my database is back to life. There were still some left over tansactions hanggin in the log and I used the script below to rollback them.
USE master; GO ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE MyDatabaseName SET MULTI_USER; GO
Thanks for reading and let me know If this ever happen to you and if you manage to solve it this way.