It has been a busy one.
We started out by replacing the DBA that left us 4 months earlier with a data analyst. With us being in a mess of an environment, I was not excited with the thought of losing my DBA partner in crime and replacing him with someone who’s not ready to just walk into that role and continue helping me grow. We hired a good guy though and he seems interested in getting to where I am someday.
3 weeks later, find me completely unplugged in the middle of the Wild and Wonderful woods with the fiancee’s family. It was the first time in 3 or 4 years that I didn’t take a laptop on vacation and had no internet on my phone. I made everyone aware at work that I would be of no use starting at 2:30 on a Monday and I’d be unreachable for the next 8 days. They managed to sneak a call in riiiiiight before I was out of cell range, but thankfully out was an easy problem I could walk them through.
I leave that vacation and jump right into another 3 days later for a long weekend in Baltimore for a golf outing. Dad and I went and stayed with a friend of his down there. First night we are some crab cakes, 2nd day was the outing and we played best ball. Finished 3rd in our division so not a bad day! Celebrated by heading to some dive bar with about 10 other golfers a couple hours then headed back to my dad’s friend’s house. His wife surprised us with a BUSHEL of crab. 8 of us sat at a table for about 3-4 hours drinking and BSing the night away while HOUSING crab. I miss that night dearly lol.
Get back to work, concentrating hard on getting the new guy up to speed. Faster he gets moving, faster I get to do cool DBA things and less data fixes and day to day fires. Theeeeeen my director put his 2 weeks in, and I felt lost. Being part of such a small operations team, I had really gotten close to him over the previous 2 years. He went to bat for me with C level employees time and time again for promotions, raises, bonuses, and education expenses. Only positive I was given was that the new director would be an internal hire. Bad news, my manager ends up getting promoted. Well, bad news for selfish me. I’m back to where I was a year ago. My manager moves to a new position and I’m left in limbo with no leadership and I gotta make some lemonade.
SQL Saturday Columbus finally comes and Lacey and I venture a couple hours west jamming in the car the whole way. I took some more PowerShell classes, a compression class, finally learned the ins and outs of reading an execution plan, and I met a ton of cool people and saw some familiar faces. Best part of the whole day was the very end when they did a couple charity auctions for the Huckleberry House. I won a year subscription to Brent Ozar’s online classes!
Next up in this crazy summer was my first SAN migration. Pulled a 24 shift and migrated about 20TB of data manually. The rest of the data was able to be moved automatically through syncing the data to both SANs then cutting over. Exhausting but I learned a ton with moving database files, directory permissions, and tools used by our datacenter to move some stuff for us.
Getting closer to current, BIRTHDAY WEEK. I had a wonderful time with Lacey who treats me way better than I could possibly deserve. Dad met me for an afternoon of Top Golf on my birthday. My friends took me to Top Golf and a local bar the next day which was a blast. Nothing work related here, just doing some quick bragging about the people I surround myself with being stupid awesome.
Now we come to the end of August, I realize I’ve spent about half my summer in a pity party about all the change above me at work and it’s time to change. I took a step back, evaluated all my tickets and projects and realized I just have to put my head down and get stuff done so I can feel accomplished at the end of the day instead of empty. Started getting into pluralsite again, picking up more PowerShell and even trying out some PostgreSQL and python to “spice it up” and try something new.
Basically this summer was fun, but as much as I am a creature of habit, I am realizing more each day that I need to reset and re-motivate myself with new experiences every couple months at a minimum in order to keep myself happy and into my career.
Well, I thought the day had finally come. My first legit corrupt database.
One production system started firing a ton of alerts for databases suspending and disconnecting. So I log into try and gather whats happening, and all my databases are missing…oh crap. Then I get an error “Database msdb cannot be opened. It has been marked SUSPECT”…DOUBLE CRAP.
Well the error says to check the SQL Logs for information, so I’ll check out what’s going on. Get the error again. Hmm, ok. Well I’ll run a CHECKDB and see if it will give me something. Still can’t interact with the db. Think Kyle, think. I start to research how to fix a SUSPECT msdb and learn that there is template version that I can restore from. After a good hour or so of googling, I take a step back and think.
Ok, so step 1 to any IT investigation I have ever done: What has changed since the issue started occurring? I asked them to remove a USB drive attached to that Hyp a couple days ago and it I got my tracking number today. Are my data files still all present? Did they remove the right drive?
Data files still existed. They removed the wrong USB but it didn’t really matter. Maybe the SAN had a hissy fit and we just need to reboot the server. *cue 8 year waiting period for this unhappy machine to reboot*.
Oh boy. System came back up, but I can’t view 2 of the drives anymore. Both are on the same SAN. Now I’m getting really confused, what do I look at now? WINDOWS EVENT LOG! There I find a different error than I expected, “Operating System error 19(Media is write protected) encountered”. Time to call the datacenter.
So I recap everything I have checked to our contact there and asked him to give things a look and tell me what he’s seeing. He tells me I’m out of space, and then I tell him that I am not. I have 400 GB between the 2 drives. It’s not great, but I am not out. What else do you think is wrong? Then I learned something VERY fun. They put a threshold in to cap my SAN usage at 95%. So instead of running out of space when I expect to run out of space, I “run out of space” faster with no warning since my disk reports aren’t configured to let me know 400GB is actually 0GB.
Around and around we go trying to come up with solutions on how to get us functioning again. They were able to add a nice 10TB NAS device to hold some of our data. With a wave of the ole dbWonderKid wand, we moved some databases, combined those 2 drives into 1, and tadaaaa a functioning mirror once again existed.
- Not all SANs are created equal. Learn your limits and alert on them properly.
- There are a few ways to fix a corrupt msdb (if it’s actually corrupt and you aren’t just out of space).
- BIG shout-out to John Grover @ MSSQLTips for the lesson if that day ever comes.
- Inspect all the logs you have available to you when an issue is encountered. I lost so much time going down rabbit holes that didn’t matter. If I would’ve jumped to Windows Logs immediately after I realized I couldn’t do anything in SSMS, I would have saved myself a good hour or so of spinning my tires.
**QUICK BRAG ALERT**
When I first started at my current company a couple years ago, I had a Junior title in front of DBA. I was given a lot of manual processes from the previous DBAs that had not yet been automated. One of the most painful tasks was always copying data from multiple production environment to a lab environment, because there were a handful of databases that needed to be moved for the application to run, required a lot of painful tracking and communication, and all was done one at a time.
This led me to my first piece of PowerShell I ever wrote. After weeks and weeks of googling between all the other normal chaos, I had something that ran and automated the majority of the work. All that was needed for me was a ticket requesting the data to be moved and I could plug some values in and hit a button. I called it the MagicDBButton, because the fact that it worked was pure magic to me.
Even this became cumbersome after a while because when development and customer support realized how much less painful it was to get the data they needed, requests came in even quicker. THERE HAS TO BE A BETTER WAY he says rocking back and forth in his cube under the desk.
MagicDBButton v2 has arrived. With the help of a member of the development team making an internal site for me, I was able to create some stored procedures, jobs, and refactor the way data gets entered into the script variables, which allows the system to automatically look for new requests every 15 minutes, email the user when the request starts and finishes, and provide errors it encounters in the email (until I find ways to automatically handle it differently). I am completely hands free for the foreseeable future. I can’t wait to hand it out to them tomorrow.
Quick brag complete. I’ll pull pieces of the code into my GitHub soon!