Friday, March 18, 2011

I have a backpack

I won it at a Microsoft Event many years ago. It says Microsoft Insider. Its an OGIO branded backpack. I have used it to daily transport my goodies to and from, between work and home. Almost daily I will grab it, with a laptop inside, some power cables, various other emergency cables of all varieties, spare mouse with batteries, an MP3 player, and so on. It has become the haven for holding all those precious things that I may need and use for electronic uses. When I go on a trip, I can typically grab this bag, throw in some cameras and camera chargers, and maybe a book, and be ready for the weekend. Whatever happens, I've got a cable or tool in that bag for what I may need. As you can tell, I practice the motto 'Be Prepared'.

Ive done this as far back as this backpack has resided in my possession. Although the actual items have shifted during said time, the existence of these items, among any others that seem necessary has been constant. This bag has a place to rest at home. A thrown if you will, where only it lives. Its near the door, so I can grab it easily on the way out the door. At work, it always goes in a similar place of glory. Always where I can get to it, see it, be comforted by its existence and all it stands for.

Whats the point of all this? We'll get there...

So, I changed jobs recently. I no longer have a laptop to bring home at night, nor to bring back to the office in the morning. I no longer have that single power cable for the laptop. My bag is a lot more light these days. But I continue to bring it back and forth. Now, the only purpose to do this is habit. The extra cables and power supplies and what nots are simply that, extra. Usually used in conjunction with a laptop. Either at the office, at home, or away. So, without a laptop in the bag, whats the point? It becomes a bag only. Not a super special device to hold all my special things. It ceases to have the purpose it once had. And I believe that it is silly for me to bring it back and forth under the old habit, since my circumstances have changed.

Get where I'm going yet? This applies to us as Database Professionals...

Sit back now, look at what you do, day in, day out. Analyze these actions and detail to yourself which of those actions serve a purpose, accomplish a goal, and which are simply formed from habit. Those that are only habitual, really dig deep on them, and tell yourself if they still serve a purpose or not. I bet you will find some that you simply do because you have become accustomed to perform said action, and they may no longer be needed. If you do not have any, congratulations. Or maybe you didnt take as honest a look at your tasks.

As for me, my bag is at home now. Still holding its collection of extra tools and what nots. But its not at the office. This entire week, i forced myself to not grab it and bring it. Forced myself out of my comfort zone to see if this tool, this crutch of a habit is really necessary. The jury is still out on that. I may need more time to fully realize the results of this small action. But I took the step to analyze myself, my actions and the usefulness of that tool. I hope you will do the same.

Monday, March 14, 2011

I'm Addicted, I'll admit it

Before I started this new job of mine, (nearing the 3 month mark as we speak) I had heard of this little tool that i envisioned would make my life easier. In this role, I am a solitary DBA, in a shop that has very little visibility into the database systems. DBs were setup, configured, and if worked, were left well enough alone. Until something bad happened, no one really looked at them.

With my new task to be the Administrator of all things Database, I was concerned that something would occur when I wasn't sitting in my chair. How could I know what was going on on the systems in the early morning hours? What about later at night, while I was unwinding from my day? And lets not even talk about the weekends... those were the worst of all. What if something happened, while I was out playing... how would I know? How could I react quick enough to solve issues, how could I be informed?

Well, all this was a concern and had been addressed when I installed, configured a special piece of software called SQL Monitor, by RedGate. Once configured to my likes, it would capture events, send me alerts, even keep track of what had happened. I was freed up to do other tasks, as I had a monkey in the background collecting and monitoring for me. I love this monkey. I have grown accustomed to having him around and letting me know whats up.

Then came this last weekend, when everything went to, well, it wasn't what I was expecting. Last week, after an extension or two of my trial period expired ( I have yet to get the money makers to get me the money to purchase the product), I was left without a window into my database. It was amazing at how quickly I was able to become addicted to this software solution and rely upon it. I had found myself checking my phone (personal cell phone) periodically when the little blip went off that let me know I had received an email from work. Sometimes it was an email from my favorite monkey (SQL Monitor) that I had left back at the office to tirelessly watch my db. Sometimes I had to deal with the alert. Sometimes not. But it was always there, swinging in its cage, keeping an ever persistent watchful eye on my systems.

However, this last weekend, my monkey stopped working, because my trial expired. Addiction at its worst. I went into withdrawal. I didn't know what was going on. I had to check it manually, periodically, to ensure things were ok.

So, what have I learned from this experience? I warn all those of you thinking about using SQL Monitor. Don't do it. I mean, don't do it, unless you are willing to allow yourself to become addicted to its features and information and alerting. Don't do it unless you want to find yourself falling in love with an imaginary monkey that watches your systems without fail when you are not in the office. Don't do it unless you like offloading the automated digging into your systems, the automated alerting of issues, the freedom that this product grants you to do other tasks while it consistently does its prescribed tasks.More importantly, don't install it, become addicted to it, rely upon it, unless you can get it purchased quickly and not become disconnected from its addictive services. This I experienced this weekend. It wasn't nearly as fun as previous weekends when it simply worked, simply monitored, simply kept my sanity in check...

I have now received an extension on the trial again. I am plugged in. I got my fix. I can last a few more days. During this time, I MUST get the monies to purchase the product, so that I can continue having my fix. Continue to get the steady drip of love from #Red-Gate and #SQLMonitor. Allow SQL Monitor reach out its virtual hands and together we can grasp the greased rope holding on to my sanity. (who's quote is this? "even the most well- adjusted person is holding on to his or her sanity by a greased rope.")

I do enjoy the product, and have become used to its usefulness and features. I have become addicted to it. I need it.

Wednesday, March 02, 2011

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 !!