SyntaxHighlighter

Friday, May 30, 2014

SQL Server: Huge Log Files and Recovery Pending Status

It's been an interesting morning so far.

We all got an email from some phantom IT department member last night (who knows who it was because it was a system account) letting us know that there was going to be some reconfiguration going on at the data center (which is hosted). It basically said, "There's nothing to see here. Move along."

So we moved along.

Last night around 8:33, I got a notification from my main production SQL Server (2008 R2 Enterprise) that the server had been restarted and moved to a different cluster node. I logged in remotely, checked it out, all looked good. I figured it was a failover or a failover test. (Incidentally, for some strange reason, our IT department sees fit not to allow the database administrator -- me -- any access to the clusters). Then at 10:45PM I got another reboot message -- same thing, but this time it was flipping back to the other node. Interesting, but I was able to get to all the data and nothing seemed amiss in the logs.

This morning, there is basically nobody from IT in the building except for myself and one single system admin. This SA has been working with a contractor on a System Center database and noticed that he couldn't get any data from System Center. He logged into SSMS and noticed that the databases were all in a state that said "Recovery Pending" and he couldn't get any information about the databases whatsoever -- no properties, no file information, nothing. So he restarted the server. Still, "Recovery Pending."

He came to me and asked me to help troubleshoot. Right away I knew that there were only a couple of paths that could cause this error message. Basically the log files for these database was missing or corrupt. However, the system databases were all online.

Step 1. Check for properties on the database. Not available. Curious.
Step 2. Check the logs. Opened up an archived log. Or tried. SSMS started choking. Okay, the log must be too big?
Step 3. Check the physical log on the disk. It was 1.2GB. Yes! You read that correctly! It was 1.2GB! No wonder SSMS was choking.
Step 4. Check the data files and the log files.

CRASH! My PC (formerly my computer) was showing a single drive -- the system drive. There used to be 3 LUNS there mapped to three different drive letters (E:, F:, G:).

Step 5. Open up the disk management tool. The LUNs were still there, they just offline. We flipped the switch and they all came online.
Step 6. Restart SQL Server.
Step 7. Verify in SSMS that the databases were online. They were.

I am not a SAN administrator not am I a dedicated Windows Server Admin, but I have not to this date seen a SAN LUN completely go offline. I'll leave that work to the the Sysadmins ...

What's the lesson here? If your databases are in "Recovery Pending" mode, one of the first things to check is whether the files actually exist -- in fact, if the drives they are on actually exist.

UPDATE: I think the log was so large because the LUN volumes went offline while SQL Server was still running. I haven't had a chance to look at the log file yet, but that's my guess.

Thursday, May 15, 2014

TSQL: Check to see if a partition function already exists OR check to see if a partition scheme already exists

When working with TSQL scripts and creating objects, it's typical to drop the object before creating it using a conditional check on it's object identifier. For example:

IF( OBJECT_ID( 'myTable', 'U' ) IS NOT NULL )
BEGIN
 DROP TABLE myTable;
END

The biggest reason for this is to avoid an error. What if the table didn't exist? If you tried to drop it without the conditional expression, you'd get an error and your script would likely halt.

But what if you're trying to drop/create an entity that isn't necessarily an object (i.e., it doesn't have an object identifier and therefore the OBJECT_ID function won't work)? You will definitely need to change your condition up a bit by querying a catalog view.

For partition functions and partitions schemes, you will query sys.partition_functions and sys.partition_schemes.

-- create a paritition function to test this out
CREATE PARTITION FUNCTION PfTest( tinyint )
AS RANGE LEFT FOR VALUES( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

-- visually inspect the partition functions table to see that it's there
SELECT
 *
FROM
 sys.partition_functions

-- drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions WHERE name = 'PfTest' ) )
BEGIN
 DROP PARTITION FUNCTION PfTest;
END

-- visually inspect the table to see that it's gone
SELECT
 *
FROM
 sys.partition_functions

The EXISTS function basically runs the query to see if there are 0 rows (hence, nothing exists) or 1+ rows (hence, something exists). If the sub-query returns a single row that matches the name of PfTest, then the condition is true and the partition function will be dropped. If it doesn't exist, then it will not be dropped.

Another option -- if you prefer cleaner and more readable TSQL -- is to create some custom functions, something like PARTITION_FUNCTION_ID or PARTITION_SCHEME_ID.

Monday, May 12, 2014

Road to Certification: 70-461 Defeated and an Analogy between Microsoft Certifications and the US Educational System

If I recall my previous post correctly, I was perfectly accepting of failure of the 70-461 exam. As I reported, last Saturday I sat through the exam knowing that failure was an option because I had the ever-helpful Second Shot voucher in play. It turns out I didn't need it. I passed the exam on the first try! That's the second pass in a row and I couldn't be happier about it. Two more exams and I'll be an MCSA (Microsoft Certified Solutions Associate).

On a related but slightly tangential note, I have been having some thoughts on the cursus honorum (Latin for course of offices, or in more common terms, the political ladder) through the certification process. Analogies are helpful and I think a comparison to our US education system will be appropriate to understand how the process works. Please note that this information is relevant for SQL Server 2012.

Level 1 - High School Diploma

MCT - Microsoft Certified Technician. This is the most basic level that Microsoft offers. You have to know quite a bit about databases to pass this test but if you've been working in the database world for a few years, it shouldn't be that hard. This won't really help you get a job just like a high school diploma won't, but it demonstrates effort and basic competency. Incidentally, elementary and middle school would be the equivalent of learning how to use operating systems and productivity applications. For an MCT in databases you have to pass one test. 

Level 2 - Bachelor's Degree

MCSA - Microsoft Certified Solutions Associate. After the MCT is the MCSA. Not even a seasoned database administrator would be able to pass these without specific training in the Microsoft SQL Server stack. Sure, knowing advanced queries would help a lot but without actually picking up some training material, you'd be very lucky to guess what SET TRANSACTION ISOLATION LEVEL SNAPSHOT actually means. This level includes a lot of theoretical material that will get you going on the basics of a lot of different aspects of querying, data warehousing, and administration. This will definitely help you get a job if you back it up with a few years of experience -- but unless you have lots and lots of experience, don't expect to be coming into the Senior level. For an MCSA in databases you need to pass three tests. 

Level 3 - Master's Degree

MCSE - Microsoft Certified Solutions Expert. This is where it gets interesting because, for the database track at least, the program actually splits off into two separate tracks -- Data Platform and Business Intelligence. Each one requires two tests. In my opinion, this coupled with a good amount of experience will definitely qualify you for a job at the Senior level in terms of database administrators or business intelligence analysts. Although many less experienced IT managers might not see the value in certifications, the ones who have been around for a long time will no doubt understand that certifications provide the theoretical underpinnings of a highly experienced professional. Sure, you may have been working on SQL Server technologies for 20 years by now, but if you don't know what you don't know then you're truly missing out on the bigger picture -- and by extension, so is the company you're working for. 

Level 4 - Doctorate

MCM - Microsoft Certified Master. Finally there's the grand-daddy of all database certifications. I've only read about this one and it sounds fairly hellacious. There was, of course, an exam or two to pass for this level but the most interesting part of it was that you had the option of spending several weeks on Microsoft's campus and learning from top-tier Microsoft product specialists and perhaps even product developers. Unfortunately, this level is gone. Microsoft Learning was not getting enough interest in this -- apparently it was a $20,000 investment and so they retired it late in 2013. Sadly, they have yet to announce a replacement. However, I'm sure that a new MCM program is in the works -- and hopefully a bit cheaper. If you ever achieve this level, you definitely deserve to be at the senior or director level in your local IT department simply because you somehow managed to talk someone in your company into shelling out the big bucks for the training. 

Level X - Honorary Degree 

MVP - Most Valuable Player. Were you the popular kid in high school? The cheerleader or the quarterback or the class president? If so, you know how these kinds of popularity contests work. It's a lot of hand shaking and baby kissing. Basically, hook or crook your way to the top and become everybody's friend and they will vote for you. I'm joking, of course, but remember that every joke has a kernel of truth. If you're at this level, you are probably working the lecture and training circuit already and you are -- in some sense -- your own boss. 
So -- you're probably thinking to yourself -- "Oh, this guy is barely in Level 2. What does he know?" Well, I know I have almost exactly 15 years of experience with the SQL Server product line dating back to the days of SQL Server 6.5. It's been a long road, of course, to get where I am. Now that I have a massive amount of experience in all three areas -- production, development, and business intelligence -- I want to fill in the gaps and get those shiny pieces of paper to prove that I know what I know. Plus, I already have an actual master's degree and I was a teacher for 4 years. So, long story short, I've been through the wringer a couple of times. Despite the fact that I'm at Level 2 in terms of certifications, I'm sitting somewhere within Level 3 in terms of actual job competency. And to be honest, I wouldn't mind being Level 4 or even Level X someday.

Saturday, May 10, 2014

Road to Certification: Pre-exam Jitters for 70-461

About a month ago I took the MCT exam for database fundamentals (98-364). I signed up a day in advance and blew through the test in less than 30 minutes and got a high passing score. For me (as for any database administrator), the exam was simple. For anyone who doesn't have a lot of experience with databases and querying, the exam would have been difficult to pass. But that's the point of these certification exams, right? To basically make sure you're at the level you say you are.

Today, in about 2 hours in fact, I'm going to take the first test in the MCSA exam series, the 70-461. I'm a bit nervous, I admit it. However, I'm not that nervous for two reasons. First, I managed to score a retake voucher -- if I fail, I get to take the exam again before May 31st. Therefore, I fully expect to fail and it's okay because I get a redo. Second, I studied -- a lot. I read the 70-461 prep book and took the practice exams. I also watched all the videos and took the practice exams on Microsoft Virtual Academy. Finally, I read through this synopsis of an actual exam taker's experiences with the exam.

What scares me a little bit is that I've found a couple of exam questions and exam review topics that point to information that I have never even heard of before today. But, as I said earlier, I'm not that worried. I get a redo. After the last exam, I got a scoring sheet that told me what areas I need to work on. So, if I fail the exam the first time (and I hear it's a fairly common occurrence), then I'll bone up on my weak areas and take it again in two weeks.

One other thing that kinda bothers me -- it's supposedly a 3 hour exam! Yikes. I'm glad I didn't schedule my post-exam, stress-relieving massage until 4 hours afterwards!