SQL Server 2008/20008R2, SQL Server 2012, SQL Server 2014, SSMS

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

Clustering, Generic Errors, SQL Server 2008/20008R2

The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group.

Let me share with you guys, an Error Message what I’ve encountered yesterday. Well, this is not a very detailed post but I hope this will help what to check for when you encounter this Error/Warning Message.

Okay, I was validating a brand new SQL Server Clustered Instance(two node) and I tried to failover the Instance to the other node. But when I try to initiate a failover, I got this Message saying “the operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group“.

What’s the Issue: The 2nd Node was not added into this Clustered Instance.(Add Node to Cluster piece was missed by whoever Installed this SQL Server Cluster)

How to Confirm?

Go to the 2nd node and look for SQL Server Binaries for respective Instance. Also you should be seeing SQL Server Instance Being Installed but in Offline Status(assuming this is your Secondary Node at this particular point of time) from your SQL Server Config Manager Oooor you can simply Open Failover Cluster Manager and check for Possible Owners(Not preferred Owners) for this Instance.

Once he added the missing node to the SQL Cluster, everything was back in business as expected.

I know it’s a Quick and a Dirty Post, But I hope this helps!

Clustering, SQL Server 2008/20008R2

How to Add a New Disk/Drive to SQL Server Failover Cluster?

Hey Folks! I know…..It’s been a long time since I wrote something technically related to SQL Server(well, am playing with my new DSLR Camera 😀 a lot now a days in leisure times) and so I’m here back with a very interesting and confusing topic for many DBA’s. Recently I had a discussion with one of my buddies on issues she had with a newly added drive to Windows Server 2008R2 Failover cluster and I was able(at least I think I was :D) to explain over our phone conversation! Later thought about coming up with a write up which might help even others in a similar situation. So, In this blog post let’s see how to add a new drive to your existing SQL Server Failover Cluster(Just an FYI : Win Server 2008/SQL 2008 in my case).

Before going any further, I’ll be wearing 3 hats in this Blogpost(a SAN Admin hat, Windows Admin hat and obviously a SQL DBA hat). Let’s start with wearing a SQL DBA hat!

Below are the screenshots of my current SQL Server Failover Cluster which I’m going to add a new Drive.

As you can see I’ve 6 SAN Drives dedicated to this cluster. Let’s see how to add a new drive with a name aaaaahhhh……say “SQLBacks2” to our SQL Server.  First thing is your SAN Admin should create/present a new Drive for you to be able to add to our cluster.

Okay, let me wear my SAN hat. Now am a SAN Admin and I’m going to create a new SAN Drive for my Windows/SQL team.

Creating a SAN Drive:

Note: If you didn’t followed my earlier Clustering Series, I use Starwind for all my SAN Stuff. Just refer to my previous posts in clustering series to understand this tool more in depth.

step1: Add a new Target.

Step 2:


Step 4: placing and sizing the Drive( I chose just 1 GB for this example)

Step 5: Making it a ISCSI aware disk(Mandatory for Clusters)

Okay..now Am done with SAN guy role and now I’m wearing  a Windows Admin Hat to initialize the new disk which my SAN Admin just created for me from my Nodes.

Wearing a Windows Admin hat:

Went to ISCSI Initiator and all I have to do is Initiate this new drive as shown below. you can see our new drive as Inactive as of now in the below screenshot.

Once you click ok, you will be seeing this drive as connected in your ISCSI Initiator, but still this is not available for Windows. Now we’ve to go to Server manager->Storage and Bring it Online->Initialize->Create new Simple Volume(Format the Drive) so that you can see it physically in your My Computer. (See below Screenshot, once I did created the drive, I selected K$ as my volume)

Once, this drive is logged on both the Nodes  basically now we’ve to add this drive to our Windows Cluster using Fail Over Cluster manager(am on Node1) as shown below.

It will search for all the disks which are suitable for clustering for a while…After few seconds, You’ll see below.

Once added, you can see this Drive has been added to our Cluster as Available Storage(Note: At this Point SQL Server Service is not yet ready using this Drive).

Now…Right Click on your SQL Server and select “Add Storage” as shown below.

So…Are we done yet? Nopee…….Here comes the most Important Part and the piece which I’ve seen people missing a lot while adding a new Drive to existing SQL Server Clusters. So what are we missing here? The Answer is SQL Server Dependency. Didn’t quite understood what I’m saying? Well, see the below Screenshot where Drive 7(new drive which we just added) is floating in air with no relation to SQL Server Service.

How to verify that the Drive has not yet added successfully 100% to our SQL Server Instance from SSMS? It’s very simple, Just try to access this Drive from your SSMS. In this example I’m just trying to take a backup of one of my Databases and looking for $K to place the backup, which I can’t see(But I can see it from My Computer) unless I add the dependency Manually as you can see below.

So…What to do now?? Just make an “AND” Dependency for your New Drive. But how??

Wearing SQL Admin hat now! Well, this could be even a Windows Admin depending on your company!

Navigate to your SQL Server and Right Click and select properties and go to dependencies tab as shown below.

As you can see, there is no Drive 7(DriveK) as dependency. Now you have to add the new drive as “AND” dependent as shown below and you are all set to go 🙂

Now see below Dependency report, where Drive 7 is no longer floating in the air 😀

and of course a happy SSMS as you would expect to locate the Drive for your Instance!

Now you are all set guys…Just try to move the Service to other node(s) and double check that everything is working as expected from all the nodes. Hope this helps! Cheers…

Mirroring, SQL Server 2008/20008R2

DB Mirroring from SQL 2008SP2 to 2008 RTM or from SQL 2005 to SQL 2008?

Have you ever thought about this? Can we Mirror a Database from SQL Server 2008SP2(Principal) to SQL Server 2008(Mirror) RTM? Well, how about Mirroring from SQL Server 2005 to SQL Server 2008??  Let me show you the prior case.

Scenario 1:

Principal DB Engine Version: SQL Server 2008 SP2

Mirror DB Engine Version: SQL Server 2008 RTM

DB Name: DB_Mirror

Answer: Yes, we can! You can see below screenshot.

You should not see any Issues with Failover/Switching DB Roles from Principal to Mirror and Viceversa.

Scenario 2: 

Principal DB Engine Version: SQL Server 2005 SP3

Mirror DB Engine Version: SQL Server 2008SP2/2008R2.

I don’t have a SQL Server 2005 Instance to show you how it works, but Yes, we can! Very Important point to consider in this case is, Once you failover(Switch the Mirroring Roles) the Database from SQL 2005 to 2008, you can’t failback! This is One-Way approach. 

Note: You can’t setup Mirroring from 2008(as Principal) to 2005(as Mirror). Well, DB Version can’t be downgraded. In other words, we can’t restore a DB to lower Versions!!

Hope this is Informative and something which you can think about as an option for upgrading/Migrating Large Databases with very less acceptable downtime! ( Make sure, you understand all the caveats before trying this in your Prod Environments).

Clustering, SQL Server 2008/20008R2, Windows Server

Cluster Logs in Windows Server 2008??

In this Blog post, let’s focus on where/how we can review Failover Cluster Logs on Windows Server 2008 and above. As most of us know on Windows Server 2003 Cluster, we used to have  “cluster.log” file on each node participating in cluster, which contains debug information. FYI, One can locate these files in “%systemroot% \ cluster” Folder. But how about cluster log files in Windows Server 2008/2008R2?? Uhuhh…It’s not something which you can review directly by navigating to systemroot folder. Below is the screenshot of that folder in my cluster.

You can see a folder called “REPORTS” in the above screenshot where all the cluster Validation Reports will be stored by default. attaching below Screenshot Just to prove, that cluster.log file can’t be located in the “reports” folder as well 🙂

Starting Windows Server 2008, cluster logs are managed by something called as “Windows Event Tracing“. Just an FYI, If you are interested, You can pull all the current  running traces by opening “perfmon” and navigating to Data Collector Sets. (Shown Below in the Screenshot)

So, as any other logs, cluster logs are stored in “C:\Windows\System32\winevt\Logs” folder with “etl” extension as you can see below.

Well, so How to read those .ETL files??

For that, we have to use “cluster.exe” command with “/gen” switch. Basically this will generate a human readable text file in your “Reports” folder.

Syntax: Cluster log /gen



As you can see in the above Screenshot, it will communicate with all the nodes in your cluster. In my scenario, Node2 is offline(Powered down).  BTW, even though Node2 is down, it will create “Cluster.txt” file in your Reports Folder with related information.

So, how to generate Logs related to a specific Node?

You have to use “/NODE” switch with your cluster log syntax. Please see below Screenshot.

As you can see, this time, we had no RPC Errors.

So, there is lot to explore/learn in 2008 Failover Clustering, if you are using 2003 since long time, things got changed drastically. There are lot of other options/switches available with cluster.exe. Even you can limit the size if you are interested. BTW, everything which I’ve shown here can be achieved via Powershell Cmdlets as well!

Hope this is informative….

Configuration, SQL Server 2008/20008R2

sp_configure and sys.Configurations

This would be a very small blogpost, Well, actually an informational Post, no Issues and no troubleshooting shown in this post!

We all, being a DBA will be running “sp_configure” every now and then on our SQL Instances to see what are the configured settings on the Instances. okay, okay….If you don’t have this Practice, make this a habit from right now. Document your Instance settings in your Run Book and compare with them each time you run this and you can analyze what/why/when/who changed accordingly.

Okay, By default if we run “sp_configure” on our Instance, we’ll be getting only 17 Rows(as of SQL Server 2012 RC0).

So are those the only configurable Settings we’ve in SQL Server? Nope!

Actually, you can get all the configurable options from querying sys.configurations, where it returns 69 Rows as shown below.

Is_Dynamic = 1 implies, no need of running RECONFIGURE Statement after changing the setting.

Is_Advanced = 1 implies, we should run RECONFIGURE Statement after changing a setting.

Now, for sp_configure to return all the 69 Options(rows) all we’ve to do is enable “show advanced options” and run “RECONFIGURE” as shown below.

Once, done issuing “SP_Configure” will return all the options available as you can see below.

Now, you can see 69 rows being returned. Cheers!