Category: Error Handling

Joining Availability Groups

Published 2017-05-18 by Kevin Feasel

Chris Lumnah troubleshoots an error in automatic seeding of an Availability Group:

In my lab, I decided to play around with the automatic seeding functionality that is part of Availability Groups. This was sparked by my last post about putting SSISDB into an AG. I wanted to see how it would work for a regular database. When I attempted to do so, I received the following error:

Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

Read on for the answer; it turns out automatic seeding itself was not the culprit.

Comments closed

Dealing With 404 Errors In Power BI Query Editor

Published 2017-05-15 by Kevin Feasel

Callum Green shows how to deal with a scenario when you try to retrieve data for a particular row but get a 404 error:

The error message is a little misleading but let’s save the debugging debate for another day. The key observation is “Guildford” data is not available, simply because it comes after “Camberley” in the list. Whilst we want to see errors in a Query, we do not want them causing data loss.

Resolution

As I mentioned at the beginning of this article, using the Remove Errors function would prevent the loss of Guildford data. However, the user needs to handle errors as Unknown Members and conform to a typical Kimball Data Warehouse.

I am sure there are many ways to fulfil the requirement, but here is how I approached it:

Read on for the resolution.

Comments closed

Missing JRE, Or Maybe C++

Published 2017-05-02 by Kevin Feasel

Meagan Longoria went through a frustrating scenario:

On a recent project I used Azure Data Factory (ADF) to retrieve data from an on premises SQL Server 2014 instance and land them in Azure Data Lake Store (ADLS) as ORC files. This required the use of the Data Management Gateway (DMG). Setup was quick and easy in our development environment. We installed the DMG for development on a separate server in the client’s network, where we also installed SQL Server Management Studio (SSMS) for query development and data validation. We set up resource groups in Azure for development and production, and made sure the settings for development and production were the same. Then we set up a separate server for the production DMG.

Deployment and execution went well in the dev environment. Testing was completed, so we deployed to prod. Deployment went fine, but the pipelines failed execution and returned the following error on the output data sets.

Weird solution, but I’m going to guess that it makes perfect sense if you are able to look at the code.

Comments closed

Error 0x80004005 In SQL Server R Services

Published 2017-04-28 by Kevin Feasel

I ran into an error in SQL Server R Services:

I recently worked through a strange error (with help from a couple sharp cookies at Microsoft) and wanted to throw together a quick blog post in case anybody else sees it.

I have SQL Server R Services set up, and in the process of running a fairly complex stored procedure, got the following error message:

Msg 39004, Level 16, State 22, Line 0

A ‘R’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004005.

Check those output variable and result set definitions.

Comments closed

Troubleshooting AG Creation Failure

Published 2017-04-25 by Kevin Feasel

Anthony Nocentino digs into logs to troubleshoot a failure when trying to create an Availability Group:

Now we have some data to look through!

When we look at the contents of the cluster logs, we’re totally on the other side of the spectrum when it comes to information verbosity. The logs so far have been pretty terse and haven’t really told us about what’s causing the failure…well dig through this log and you’ll likely find your reason and a lot more information. Good stuff to look at to get an understanding of the internals of WSFCs. Now for the the reason my Availability Group creation failed was permissions. Check out the log entries.

It’s a good troubleshooting guide.

Comments closed

SQL Server Backup To Azure Tool Causing Restore Errors

Published 2017-04-13 by Kevin Feasel

Jack Li diagnoses an issue in which the Microsoft SQL Server Backup to Microsoft Azure Tool causes errors when trying to restore a database on an Azure VM with SQL Server 2008 R2:

I worked on an interesting issue today where a user couldn’t restore a backup. Here is what this customer did:

backed up a database from an on-premises server (2008 R2)

copied the file to an Azure VM

tried to restore the backup on the Azure VM (2008 R2 with exact same build#)

But he got the following error:

Msg 3241, Level 16, State 0, Line 4
The media family on device ‘c:\temp\test.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 4
RESTORE HEADERONLY is terminating abnormally.

We verified that he could restore the same backup on the local machine (on-premises). Initially I thought the file must have been corrupt during transferring. We used different method to transfer file and zipped the file. The behavior is the same. When we backed up a database from the same Azure VM and tried to restore, it was successful.

Click through for Jack’s findings as well as a couple workarounds.

Comments closed

Rethrowing Exceptions

Published 2017-04-12 by Kevin Feasel

Vladimir Oselsky shows how to use THROW and RAISERROR for rethrowing exceptions:

Upon executing the first procedure, we get the error message back to the front end, but after checking balance, we find that money withdrawn from the account, but in the case of the second procedure, the same error returned to the front end but money still there.

Now we begin to scratch our head trying to figure out why we lost the money even though we got errors in both cases. The truth behind is the fact that RAISERROR does not stop the execution of code if it is outside of TRY CATCH block. To get same behavior out of RAISERROR, we would need to rewrite procedure to look something like following example.

There are some nuanced differences between THROW and RAISERROR, so it’s valuable to know how both work.

Comments closed

Handling Runbook Alerts

Published 2017-04-05 by Kevin Feasel

Grant Fritchey shows how to set up alerting when an Azure automation job fails:

Believe it or not, there’s not an immediately obvious “Oh, you had an error in your Automation script, here’s how you alert someone” setting in the Azure portal. Now, you could simply put error handling in your PowerShell script. In fact, it’s probably not at all a bad idea to do that as well. However, what you would not get setting things up that way is a mechanism for managing the alerts, history, additional possible responses (like firing off another Runbook, although there is way to do that from the PowerShell too). Instead, what I want is way to manage alerts through the Azure fabric.

If you do a search, there is an Azure Alert service. However, it didn’t seem to be really what I was looking for. Further, I found it extremely difficult (OK, I couldn’t make it work) to connect the alerts directly to the Jobs related to my Runbooks. Instead, after quite a bit of research, what I found is a combination of Azure Log Analytics with the Operations Management Suite (OMS) will do exactly what I’m looking for.

Click through to read how to set this up.

Comments closed

Troubleshooting Cluster Creation Errors

Published 2017-04-05 by Kevin Feasel

Mark Broadbent diagnoses an error which seems misleading at first:

One such problem is when you use the New-Cluster command to add all your nodes in one go.
New-Cluster -Name magrathea -node server5,server6,server7 
-staticaddress 192.168.1.70
Simple right? Well no. In this instance I ran into the following error:
New-Cluster : There was an error adding node 'server7' to the cluster

the node cannot be contacted. Ensure that the node is powered on and is connected to the network.

Read on for an example of piecemeal debugging. Mark’s advice is to keep things simple, as in this case at least, you can’t count on the error messages coming back to be completely accurate.

Comments closed

Operating System Error 3

Published 2017-03-31 by Kevin Feasel

Stacy Brown provides common reasons for why you might get Operating System Error 3:

Sometimes the users of SQL Backup Master may face the following error while executing the database backup job:

Msg 3201, Level 16, State 1, Line 1
Job Execution Error: Cannot open backup device ‘’ Operating System error 3 (The system cannot find the path specified.)

Now, there can be the various possible reasons behind the occurrence of this error. Therefore, in the following sections, all possible reason with their respective solutions are discussed. A user can refer them to solve this SQL Server operating system error 3(the system cannot find the path specified.)

Click through for solutions to several potential causes of this error.

Comments closed