Press "Enter" to skip to content

Category: Error Handling

One…Million IO Requests

Published 2021-05-24 by Kevin Feasel

Sean Gallardy wins the jackpot:

If, somehow, you’ve managed to see this error in your errorlog then congratulations, you’ve won an instance of SQL Server that probably won’t be doing much.

I found out about this message a few months ago, but it has been in the product for years and I went this long without ever even knowing it existed (congrats me!) until I was asked about it and coincidentally ended up finding it in an errorlog the same week. Clearly, I have too much fun packed into my weeks. I asked around, only one other person had ever found this in an errorlog before… that’s either impressive, depressing, or some perfect quantity of both – mellow it out to a smooth melancholy.

Click through to see more information about the 1000000 IO error message and when you might find it.

Comments closed

Errors and Return Codes in SQL Agent Powershell Job Steps

Published 2021-05-17 by Kevin Feasel

Ron the Polymath has a framework:

PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):

Read on for those interesting points, for a block of Powershell code you can use to track errors, and a SQL Agent job template to boot.

Comments closed

VS_NEEDSNEWMETADATA in SSIS

Published 2021-05-14 by Kevin Feasel

Hadi Fadlallah discusses what was the bane of my existence for about 3 months in 2010:

In this article, we will briefly explain the VS_NEEDSNEWMETADATA SSIS exception, one of the most popular exceptions that an ETL developer may face while using SSIS. Then, we will run an experiment that reproduces this error. Then, we will show how we can fix it.

This was really annoying prior to SQL Server 2008 (at least, that’s my early-morning recollection of when the SSIS engine started trying to auto-fix this) and has been mildly annoying since. I had far too many conversations which I could summarize as “Yes, I understand that this Excel spreadsheet is basically the same, but it’s different in that the casing on one header column has changed slightly and that breaks the entire system.

Comments closed

Cannot Recover the Master Database

Published 2021-05-12 by Kevin Feasel

Erik Cobb takes us through an undesirable experience:

Recently, during patching for a 2019 SQL Server, the SQL services refused to start after the patching. It was throwing the following heart attack inducing error:

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.

This is not the first time I have seen this error. Actually, this seems to be the default error that Microsoft throws any time there is a problem with the patching process. The good news is, most likely this is a false error and the master database is perfectly fine.

Read on to learn more.

Comments closed

Fun with MERGE and Deadlocks

Published 2021-05-11 by Kevin Feasel

Daniel Hutmacher walks us through another reason to avoid using the MERGE operator:

I recently ran into a curious deadlock issue. I have a process that performs a lot of updates in a “state” table using multiple, concurrent connections. The business logic in the application guarantees that two connections won’t try to update the same item, so we shouldn’t ever run into any locking issues. And yet, we keep getting deadlocks.

What’s going on here? Hint: it has to do with isolation levels and range locks.

Read on for the problem-causing query and a few ways to resolve the problem.

Comments closed

Surviving a Kafka Outage

Published 2021-05-05 by Kevin Feasel

Jakub Korab walks us through availability features in Kafka as well as what to expect if your brokers are unavailable:

In the case of an outage, you have to ensure that these messages can be processed eventually. Keeping unsent messages around and retrying indefinitely in the hopes that the outage will rectify may eventually result in your application running out of memory. This is a crucial consideration in high-throughput applications.

If business functions are performed by systems downstream of Kafka, and the sending application only acts as an ingestion point, the situation is slightly more relaxed. If Kafka is unavailable to send messages to, then no external activity has taken place. For these systems, a Kafka outage might mean that you do not accept new transactions. In such a case, it may be reasonable to return an error message and allow the external third party to retry later. Retail applications typically fall into this category.

Read the whole thing.

Comments closed

Windows Server Failover Clustering Error Code 5054

Published 2021-04-09 by Kevin Feasel

Josh Darnell walks us through an error when setting up an Availability Group:

For setting up the environment, I was following this really in-depth guide from former Data Platform MVP and current Microsoft employee Ryan J. Adams: Build a SQL Cluster Lab Part 1

The guide is generally fantastic, and provides a lot of good insight into the non-SQL Server related aspects of setting up an Availability Group. I’d highly recommend checking it out if you’re interested in that sort of thing.

Relevant to this post, he has provided a diagram of how the different networks are configured:

If you’re very experienced with networking, you may already have some idea of what the problem is going to be. Don’t spoil it for everyone else okay?

I’ll admit I did not have an idea of what the problem was.

Comments closed

IDENTITY Overflow in SSIS

Published 2021-04-08 by Kevin Feasel

Alex Stuart hits a weird error:

Conversion/overflow errors aren’t that unusual – normally a data flow broken by some unexpected data (“no, there’s no chance that field would ever have a character in it”), or perhaps a column hitting max size (“INT will be enough for years, like, 5 years. I’ll have left the company by then”)

But that wasn’t the case here – the package and user tables involved were checked by the dev team and there was no possible overflow. I’d checked system databases for maxed-out identity columns and found nothing. Heads were scratched.

Read on for the post-head-scratch answer.

Comments closed

THROW and Linked Servers

Published 2021-04-07 by Kevin Feasel

Chad Baldwin hits on an interesting result when using THROW across a linked server:

The THROW command is non-terminating if it is used in a stored procedure over a linked-server.

I don’t know the details to why it works this way. The THROW command returns an error message with a severity level of 16, which, according to my RAISERROR Cheatsheet, does not stop execution.

There’s something special about the THROW command beyond raising an error message. Behind the scenes, there is likely some extra information being passed to tell SQL Server that execution needs to stop in that moment, and that extra bit of information does not appear to be passed between linked servers.

Click through for a demo.

Comments closed