A Fine Slice Of SQL Server
Reitse Eskens runs into a problem:
This warning felt benign; more like you can run into errors. Well, as I found out, this lets you run into an SQL Server installation that just fails.
The error logs fill up with stack dumps and a fatal error that makes no sense. The installation log, however, shows you a more meaningful error.
Click through for the error message, as well as one way to fix the problem.
Leave a CommentWe were setting up a brand-new SQL Server 2022 instance and couldn’t connect remotely using valid Windows credentials. Every attempt gave us the same error: “Login is from an untrusted domain and cannot be used with Windows authentication.” The client only has one domain. Permissions and firewalls checked out. Local connections with domain accounts worked fine. So why was the server rejecting us from every remote machine, no matter how we connected? The answer turned out to be a change in Windows Server 2025 that more and more DBAs are going to run into.
My advice for every DBA is as follows: make sure you have at least a rudimentary understanding of SPNs, including what they are, what can go wrong when they aren’t set correctly, what “set correctly” even means, and how to set them. You don’t need to be an expert on Kerberos, but I think you do need to be a technician who can note a specific error code and troubleshoot the issue from there.
If you ever had to deal with SSRS or SSIS double-hop issues, you’ve likely already dealt with SPNs in some fashion. Just bite the bullet and spend a few hours boning up on the topic.
Leave a CommentSean Gallardy troubleshoots an error:
The symptoms of this issue were interesting, every so often the instance would just kind of get “stuck” – at least that is how it was described to me. Nothing would work, cancelling queries, attempting to kill queries, submitting anything new, nothing seemed to really do anything except restarting the service. Once the service was restarted, the instance (and AG) would hum along nicely… until some random time later when submitting different queries would just grind to a halt. Fun.
The answer is just as dumb as you’d think. But I won’t spoil the punch line here.
Leave a CommentSSIS packages provide great flexibility for integration between systems, but when they go wrong you can end up digging through logs or reports because every package logs differently. A standarised framework for tracking failures can drastically cut down troubleshooting time.
I reminisced recently about old code, I said “it’s not enough to make it work correctly. It needs to fail correctly too”. So in this post we’ll demonstrate a simple way to consistently track errors and failures in packages to help make troubleshooting much easier.
My recollection is that this kind of failure logging is less important if you have the SSISDB catalog, as it collects a lot of the information as well. But then again, I haven’t really used SSIS in a while, so that memory could be fuzzy.
Leave a CommentHaki Benita digs into a problem:
Here’s a database riddle: you have two tables with data connected by a foreign key. The foreign key field is set as not null and the constraint is valid and enforced. You execute a query that joins these two tables and you get no results! How is that possible? We thought it wasn’t possible, but a recent incident revealed an edge case we never thought about.
If you answered “READ COMMITTED isolation level is weird,” congratulations: you win a prize.
Leave a CommentOlivier Van Steenlandt runs into an issue:
Over the past few weeks, I have been doing some experimenting with Azure SQL mirroring to Microsoft Fabric. In the process, I ran into a couple of issues and challenges. In this data recipe, I will be going through one of my challenges when I got Azure SQL mirroring to Microsoft Fabric setup and running.
At first, everything seemed to be working as expected, and the integration felt very smooth. At that point, I continued to develop my test database in Azure SQL to learn a bit more about mirroring. I made a couple of minor changes to my test database and tried to publish them from my SQL Database Project.
Read on for the issue, as well as the solution—that is, assuming you don’t actually want to change any of the things you’re actively moving over into Fabric.
Comments closedAndy Brownsword thinks about successful code:
A common time to revisit old code is when something breaks. I was contemplating Pat’s question this week when a field length change had caused a truncation issue in an old SSIS data flow. Some code doesn’t age badly because it’s wrong, but because it didn’t expect to fail.
It’s all too easy to write a piece of code which ‘does the thing’, run a few variant tests, and send it on its way. But will it stand the test of time? That’s where my good and bad code diverge, and I usually revisit the code and find the old milk.
Admittedly, I’m not as good at this as I should be either. It can be a challenge to think through the possible issues that could arise and develop code to mitigate or eliminate those issues. But as Andy points out, it can be critical to success.
Comments closedRich Benner doesn’t consider “remove the index” a valid solution:
The client noticed the D (data) drive was running out of free space and they asked us to investigate. We found that the SQL Logs folder was much larger than we’d expect. A considerable amount of this data was not database files (.mdf, .ldf, .ndf), but rather log files (.log and anything with a .Number file extension):
Read on for a bit of a shaggy dog story, as most IT stories tend to be. You start with one problem (almost out of disk space) and it turns into a cascading series of problems, so that by the end of things, you’re trying to figure out how to diagnose an error message when installing Node 16 on a Windows 7 laptop.
Comments closedErik Darling performs a rather late Airing of Grievances:
In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.
Click through for the video.
Comments closedMark Beaumont diagnoses an error:
Recently, one of our clients encountered an issue while running a data update in SQL Server. The operation failed immediately with a configuration error, specifically targeting Large Object (LOB) data:
Length of LOB data (169,494) to be replicated exceeds configured maximum 65,536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65,536. A configured value of -1 indicates no limit, other than the limit imposed by the data type.
The tricky part was, that client wasn’t using replication. Read on to learn about the culprit.
Comments closed