It was a dark and stormy night…
Regularly scheduled maintenance window to apply patches and reboot the server had just transpired, but one of the databases was taking a really long time to recover. The handful of usual suspects, VLFs, vehemently denied any involvement, but I caught error 823 red handed.
“‘Device not ready’, is that your story?” I asked. I got no reply.
The drive was accessible. The file was there. I saw it with my own eyes.
ALTER DATABASE SET %anything% only returned error 5011 level 14 state 7, drat!
The full database backup and his two tranlog backups assured me that they had my back. Drawing my courage from their strength, I had a plan:
1. Stop the SQL Service
2. Rename the existing tranlog file
3. Start the SQL Service
4. Database will be in error (for real, because the file will really be missing this time)
5. Stop the SQL Service
6. Rename the existing tranlog file back to its original name
7. Cross fingers, pray, hope
8. Start SQL Service
HOWEVER, at step 4, the database was ONLINE!???!! What the….?!
Yep. ONLINE. Looked at database properties and low and behold, a brand new, shiny transaction log sitting where Model said that it should…on a completely different drive than where the tranlog file was originally. Poor tranlog. Looking so awkward and out of place with all the other files looking at him like he belonged in a freak show or something…but I digress.
It took me a little while to figure this out, but it seems when the database is in bulk-logged recovery, and the transaction log file goes bye-bye, a new one will be created for you.