The log for database 'Borked' is not available. Check the event log for related error messages. Resolve any errors and restart the database

I found that a small database across the country that no one really thinks about often was giving this message. I was made aware of this thru a series of events that led me down quit the rathole. From one thing to the next and the next, and finally finding this to be a core issue in the event logs.

Its a little database. It does a small specific task.Let's call it 'Borked'. It was part of a maintenance plan that performed backups, and since the log file was unavailable, it was unable to backup. Luckily this was part of a maintenance plan, so when it failed at the beginning of the process, it also prevented any other databases from backing up. Yes, that was facetious. This was one of the items along the rathole that I found out. The issue came to my attention as folks noticed that some data was out of sync. Borked DB that lives on this db server is copied elsewhere and restored, and reports are pulled from it. This data on the report was out of whack. Because there were no new backups of DB X, because DB Y had an issue... part of maint plan... yeah...

So, I do some research online, and see people suggesting DBCC CheckDB on this DB. However, when I attempted to run this command, it would tell me the same error. Odd. Looking into the errorlogs shows me that its been happening for quite some time, about 20 days, and the first instance of it showed me nothing of note in the Error Logs that could point to any other issue. There are 4 other dbs on this DB Server, and they are all ok. Before touching this Borked DB, I ran DBCC CheckDB on all the others, and performed manual backups. I also removed the maint plan and instituted individual backup jobs. I tested each of these, and the next day confirmed that they executed properly. So, all was well with the other DBs. But nothing seemed to work on Borked.

Being worried that there is some corruption, that I may loose data, coupled with the fact that 20 days have transpired without a descent backup, I was obviously worried about the data. I created a new database, and exported all the data from Borked to BorkedData db, to keep track of it. I blew it on this step, since I had not initially created the objects as they were in the original, they were stripped down copies of the tables (missing Identities, missing constraints, etc). So now I have a copy of the data, but the schema is a bit goofed. I take some more time to actually use #RedGate tools to make a copy of the database, then export the data, then compare again to ensure that me BorkedNew database has all the proper objects and data. Whew.

Now I am free to futz with the Borked. Obviously some tasks have been attempted already, but they all resulted in an error message about a missing log file. Not much more help than that. So my obvious thoughts went to detaching the DB, and bringing it back online thru attaching it again. This seemed to make sense in my head, and was the direction I had headed since the inception of messing with Borked. I had to get my ducks in a row to ensure that I could restore it, if the need arose. Thus all the moving of data, copying of schema, etc. Just to make sure I had a copy. So, I get ready to detach Borked and reattach it. I create all the scripts, double check the paths, and simply make sure all was in order. I envisioned that this would be a quick 1-2 punch. Detach. Attach. Viola, no one the wiser. Keep in mind that there is a web app using the DB, but its not highly used, so a small interruption would not be noticed, or so I was told by the 'owners' of this system. I use the term 'owners' in the loosest sense of the term. I have now spent more time vested with Borked than anyone else in the company, so technically, I am its new 'owner'. Anyway, I digress. We are about to detach and attach the db, which in my mind will release it, and then upon attaching it, will reinit the link to the log, and somehow make life happy again for little Borked. I pause, looking over the script one last time, ensuring I have selected the appropriate T-SQL... pause for effect, then execute the fateful code.

I know, you are expecting that this is the end of this story... But no. It is not. What happened? same thing as all other attempts... Error about the missing tlog. Crap. Crap Crap. Ive prepped, Ive backedup. Ive copied data. Ive ensured I can restore. I have the entire DB copied elsewhere. and that backedup. Yeah. What to do now.

So, I right click it, and goto tasks in SSMS, and notice the small menu item I have rarely used. 'Take Offline'. Hum, not quite a Detach. Not quite a Delete. Something I've honestly, rarely performed on a DB. So I click it. And sure enough, DB goes offline. There is a nice little icon next to it that shows me its offline. Well, at this point, it seems that there is only one choice. That is to 'Bring Online' again. So I now click this. And viola, Borked is now online.

I go to the script that I had saved that had all other attempted commands I had previously executed against Borked. I ran a DBCC CheckDB against it, and it succeeded. I then realized I had fodder for a Blog post, and started writing this. About the time that I wrote the word 'Take Offline' in the paragraph above, I actually went back to the DB to ensure that was the proper term, and realized I had yet to backup this database, after bringing it back online. Doh! What a dork. So i obviously kicked off the backup, and it succeeded.

Viola! my little Borked db is now online, and all seems well. Had I known this little caveat, I might have performed those simple menu items yesterday, instead of worrying about getting backups, moving data, digging into the processed, and all that. But, the silver lining is that this little db Borked now has a new owner. One that knows more about it than others, and will love it for years to come. Even if it cant figure out where its tlog went and why it can no longer access it. Fun Times !!