Error Handling – Curated SQL

Finding Missing SQL Server MSIs

Published 2019-12-11 by Kevin Feasel

Annette Allen had a service pack installation go south due to missing MSIs:

I was recently doing a service pack, I’d run it on the entire test estate and half of the Production estate, I’d used Pinal Dave’s really useful AG check list and been really overcautious, I’d finished integrity checks on all databases, I’d backed everything up and even had a snapshot of the server completed.
When I clicked on the service pack I got the error “missing MSI” sorry I don’t have the screen dump or the full error message because at the time of trying to fix it I did’t think to take a copy

Read on to see what the root cause was and how Annette was able to fix this error.

Leave a Comment

Conversion Failed when Converting ‘NULL’ to Int

Published 2019-12-06 by Kevin Feasel

Kenneth Fisher has a fun error for us:

I love this error. Primarily because it demonstrates two very important things.
1. Errors matter. Make sure when you ask someone for help you give them the exact error and circumstances causing the error.
2. Experience matters. If you’ve been working with database development for a while you can probably pinpoint exactly what’s causing this error just from the error.
When I was handed this error from one of my co-workers I started by telling them exactly what was wrong, and then out of curiosity started a quick poll.

The answer is about as straightforward as it gets, and yet a pretty good percentage of people won’t get it on the first try.

Leave a Comment

Failing SQL Server 2019 Installation

Published 2019-11-27 by Kevin Feasel

Paul Randal walks through an installation error with SQL Server 2019:

I have a brand new Windows 10 laptop that I use solely for teaching, as the HDMI and SVGA connectors on my main laptop somehow have bad connections to the motherboard after a few years of traveling around the world. On the new laptop I have SQL Server 2017, and now that SQL Server 2019 RTM has shipped, and I’ve finished teaching for the year, I set out to install 2019 side-by-side with 2017 so I can move my teaching environment to 2019 and work on demos of the new features pertinent to what I teach.
Thinking this would be a smooth process, I kicked off the install wizard, went through it, hit go, and walked away. I came back half an hour later to see that all install steps had failed.

Read on for Paul’s solution.

Leave a Comment

Memory-Optimized Tables and Error Log Entries

Published 2019-11-27 by Kevin Feasel

Shaun J. Stuart points out impoliteness on the part of In-Memory OLTP:

It’s nice that they are labeled with [INFO], so you can be fairly sure they aren’t a major issue, but they still annoyingly fill up the log with information that is of no use to anyone outside of Microsoft. It would be nice if you could disable these messages but, to my knowledge, you cannot.

These are the types of error messages which should, by default, not write to the error log. My real bugbear is “Hey, we successfully backed up the transaction log!” You should not need a trace flag to turn that off; you should need one to turn it on for diagnostic purposes.

Leave a Comment

Debugging Azure Data Factory Data Flows

Published 2019-11-11 by Kevin Feasel

Mark Kromer takes us through debugging Azure Data Factory Data Flows:

When you are designing your mapping data flows in ADF, you are working against a live Azure Databricks Spark cluster. The size of that cluster is configurable via the Azure Integration Runtime. If you do not configure a custom Azure IR, then you will use the default Azure IR. That sets a very small cluster size by default of 4 cores for a single worker node and 4 cores for a single driver node. In most cases, while debugging and using data preview, that should be fine. But when you start exploring your data with column statistics or increase the sampling size in debug settings, you may find that you’ve exceeded the capacity on that small default cluster. Below are the steps you need to take to increase the size of your debug cluster.

Click through for step-by-step instructions.

Comments closed

RSExecRole Already Exists

Published 2019-11-04 by Kevin Feasel

Dave Mason troubleshoots an annoying error:

When migrating an instance of SSRS, I performed a backup of the [ReportServer] and [ReportServerTemp] SSRS databases from a SQL Server 2008 R2 instance and restored them to a SQL Server 2017 instance. After installing SSRS 2017 on the target machine, I ran SSRS configuration and attempted to set the Current Report Server Database to the existing [ReportServer] database I had already restored:

Read on to see the error and Dave’s fix. As I get older and more cantankerous, I realize even further the benefit of rerunnable scripts and repeatable processes. They prevent so many errors of this sort.

Comments closed

Azure Kubernetes Service Max Volume Count

Published 2019-09-23 by Kevin Feasel

Chris Taylor explains an error message in Azure Kubernetes Service:

Whilst playing around with my session for Techorama.nl I encountered an error I hadn’t seen previously whilst deploying SQL Server on Linux in Azure Kubernetes Service (AKS)
0/1 nodes are available: 1 node(s) exceed max volume count
The yaml I used was only slightly modified (mainly names) from scripts used on minikube and docker-desktop so I was a little confused as to why I was getting this in AKS.

Read on to understand what’s happening here and how you can fix it.

Comments closed

Intellisense and the DAC

Published 2019-09-09 by Kevin Feasel

Slava Murygin doesn’t like severity 20 errors just popping up for no good reason:

Yesterday I’ve needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an “Severity 20” alert.

As you probably know, Severity 20 Errors “Indicate system problems and are fatal errors” (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though “Severity 20” does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

Read on to see the cause of Slava’s problem and how there’s no way to fix it in SSMS.

Comments closed

DBCC CHECKDB Error on Azure SQL Database

Published 2019-08-26 by Kevin Feasel

Arun Sirpal explains an error message on Azure SQL Database:

msg 7928, Level 16, State 1, Line 3
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 8921, Level 16, State 3, Line 3
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Read on to see what this means, as well as what it means for you.

Comments closed

Reading SQL Server Error Logs

Published 2019-08-19 by Kevin Feasel

Thomas Rushton has a script for us:

Why Script This? What’s Wrong With SSMS’s GUI?
Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I guess the whole thing is just a wrapper around xp_readerrorlog below…

But Thomas has a better way for us.

Comments closed

Category: Error Handling