Press "Enter" to skip to content

Category: Error Handling

Availability Group Seeding and Transient Failure 108

Published 2024-12-19 by Kevin Feasel

Chad Callihan runs into an error with an availability group:

The availability group in question was unhealthy, and none of the added databases were syncing. By the time I started investigating, the SQL service on the secondary had been restarted. There were also no recent errors in Failover Cluster Manager.

I checked the SQL Server Error Log and found some clues. The SQL Server Error Log was filled with “Always On: DebugTraceVarArgs” errors for each database that included the message:

“Seeding encountered a transient failure ‘108’, retrying…”

Read on to see how Chad fixed this.

Leave a Comment

Sending Alerts from Fabric Workspace Monitoring

Published 2024-12-16 by Kevin Feasel

Chris Webb has a new Bat-signal:

I’ve always been a big fan of using Log Analytics to analyse Power BI engine activity (I’ve blogged about it many times) and so, naturally, I was very happy when the public preview of Fabric Workspace Monitoring was announced – it gives you everything you get from Log Analytics and more, all from the comfort of your own Fabric workspace. Apart from my blog there are lots of example KQL queries out there that you can use with Log Analytics and Workspace Monitoring, for example in this repo or Sandeep Pawar’s recent post. However what is new with Workspace Monitoring is that if you store these queries in a KQL Queryset you can create alerts in Activator, so when something important happens you can be notified of it.

Read on to learn more.

Leave a Comment

Unique Constraint Violations Cause Bloat in PostgreSQL

Published 2024-12-05 by Kevin Feasel

Josef Machytka shows something annoying:

The issue of table and index bloat due to failed inserts on unique constraints is well known and has been discussed in various articles across the internet. However, these discussions sometimes lack a clear, practical example with measurements to illustrate the impact. And despite the familiarity of this issue, we still frequently see this design pattern—or rather, anti-pattern—in real-world applications. Developers often rely on unique constraints to prevent duplicate values from being inserted into tables. While this approach is straightforward, versatile, and generally considered effective, in PostgreSQL, inserts that fail due to unique constraint violations unfortunately always lead to table and index bloat. And on high-traffic systems, this unnecessary bloat can significantly increase disk I/O and the frequency of autovacuum runs. In this article, we aim to highlight this problem once again and provide a straightforward example with measurements to illustrate it. We suggest simple improvement that can help mitigate this issue and reduce autovacuum workload and disk I/O.

I’ll sometimes create unique constraints in SQL Server and set the flag to ignore duplicates, most often in queue tables or situations where I know whatever gets inserted will always be the same, but that multiple agents could act to insert a record and the calling code doesn’t have any sort of protection. The idea that this pattern can hurt you in PostgreSQL seems weird to me.

Leave a Comment

Temp Tables in SSIS Data Sources

Published 2024-12-02 by Kevin Feasel

Andy Brownsword disappears in a flash:

When handing data we can make use of temporary tables to aid with separation or performance. However, they don’t always play nice with Integration Services packages.

If we set a source to call a procedure returning the contents of a temporary table we’ll see an error like below:

Read on for three options. It’s been a while, but I vaguely recall that you can use global temp tables (such as ##Results) and it will work, as those persist and are available to all sessions so long as there is some open session using them.

Leave a Comment

Debugging in Databricks

Published 2024-11-19 by Kevin Feasel

Chen Hirsh enables a debugger:

Do you know that feeling, when you write beautiful code and everything just works perfectly on the first try?

I don’t.

Every time I write code It doesn’t work in the beginning, and I have to debug it, make changes, test it…

Databricks introduced a debugger you can use on a code cell, and I’ve wanted to try it for quite some time now. Well, I guess the time is now 

I’m having trouble in finding the utility for a debugger here. Notebooks are already set up for debugging: you can easily add or remove cells and the underlying session maintains state between cells.

Comments closed

Query Processor Ran out of Internal Resources

Published 2024-10-31 by Kevin Feasel

David Fowler explains an error:

Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error,

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I’ll be honest, that’s not one that I had seen before but it seemed pretty self explanatory. the query was just too complex for SQL to cope with. I asked what the query was, the answer was something similar to the snippet below,

Read on to learn what the problem was, as well as David’s answer. David had a simple rewrite retaining the IN clause, though you could also rewrite this with an INNER JOIN or even an EXISTS. One of those two alternative approaches might have a better performance profile, though there are no guarantees.

1 Comment

Timeouts Attempting to Open Connections from High-Thread Applications

Published 2024-10-31 by Kevin Feasel

Jose Manuel Jurado Diaz works through a customer issue:

Recently, I worked on a service request that a customer application reported the following error connecting to the database: “Timeout attempting to open the connection. The time period elapsed prior to attempting to open the connection has been exceeded. This may have occurred because of too many simultaneous non-pooled connection attempts.“. 

Following, I would like to share the experience learned here.

The issue isn’t extremely common, but it does happen, especially when applications don’t use connection pooling.

Comments closed

Configuring Database Mail in Azure SQL MI

Published 2024-10-23 by Kevin Feasel

Andy Brownsword sends an e-mail:

SQL Agent jobs allow us to schedule and automate tasks on a SQL Server instance. Crucially, when things go wrong we need to know about them. That’s why we use notifications.

Setting up Operators and job Notifications is as expected on a Managed Instance. However, when it comes to sending the notifications we may have a challenge, as shown in the SQL Agent Error Logs:

Read on for the solution.

Comments closed

Troubleshooting Chains of Common Table Expressions

Published 2024-10-17 by Kevin Feasel

Jared Westover calls me out:

I started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I’m less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it’s someone else’s code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?

This is, I would argue, the biggest inconvenience around using common table expressions. The fact that SQL Server cannot materialize the contents of CTEs is likely a more prevalent problem, but challenges in how you can troubleshoot the individual parts of common table expressions are real.

Comments closed