Press "Enter" to skip to content

Category: Error Handling

Investigating Full-Text Index Issues in SQL Server

Published 2026-02-06 by Kevin Feasel

Rich 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 closed

NOWAIT Hints and Annoyances with Query Store Hints and Plan Guides

Published 2026-02-04 by Kevin Feasel

Erik 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 closed

LOB Data and Replication in SQL Server

Published 2026-01-23 by Kevin Feasel

Mark 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

Dealing with a Full Transaction Log

Published 2026-01-16 by Kevin Feasel

Rebecca Lewis performs some troubleshooting:

It’s 2am. Your phone wakes you. Rub your eyes, check your email, and there it is:

Error: 9002, Severity: 17, State: 4
The transaction log for database 'trading' is full due to 'LOG_BACKUP'.

The database is still online. Looks ok. You can read from it. But every INSERT, UPDATE, and DELETE fails. Production night-trading is effectively down.

The good news: It’s fixable — but, that fix depends entirely on what’s preventing log truncation.

Click through for a choose-your-own-adventure story.

Comments closed

Lessons Learned in a SQL Server 2025 Upgrade

Published 2026-01-15 by Kevin Feasel

Aaron Bertrand shares some lessons learned:

We recently upgraded multiple systems to SQL Server 2025. The engine upgrade itself was smooth, but three unexpected issues surfaced in our lower environments as we planned out production. None of these issues prevented the upgrade from completing, but all three could easily derail an otherwise smooth in-place upgrade to SQL Server 2025. What were these issues, and how can you avoid hitting them?

My biggest surprise out of this is that full-text search actually got upgraded.

Comments closed

Sequence Integer Overflows and BIGINT in PostgreSQL

Published 2026-01-13 by Kevin Feasel

Laurenz Albe performs a migration:

In a previous article, I recommended using bigint for sequence-generated primary keys (but I make an exception for lookup tables!). If you didn’t heed that warning, you might experience integer overflow. That causes downtime and pain. So I thought it would be a good idea to show you how to monitor for the problem and how to keep the worst from happening.

Read on for the downtime-rich solution (thanks to table blocking), as well as a solution that requires less downtime.

Comments closed

Diagnosing DirectQuery Connection Limit Issues

Published 2025-12-31 by Kevin Feasel

Chris Webb goes troubleshooting:

To kick off my series on diagnosing Power BI performance problems with Performance Analyzer in the browser (which I introduced last week with my post on vide-coding a custom visual to visualise Performance Analyzer data), I want to revisit a subject I blogged about two years ago: how hitting the limit on the maximum number of connections to a DirectQuery data source can lead to queries queuing for an available connection and performance problems. In my original post on this topic I showed how you can use the Execution Metrics event in Profiler/Log Analytics/Workspace Monitoring to see when this queuing happens. In this post I will show how you can do exactly the same thing with Performance Analyzer.

Read on to learn how.

Comments closed

Diagnosing SQL Audit Failure

Published 2025-12-22 by Kevin Feasel

Alyssa Montgomery troubleshoots an issue:

Message: 

SQL Server Audit failed to create an audit file related to the audit ‘AuditName_ServerAudit’ in the directory ‘C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log’. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file. 

Based on the error, the solution would be to free up drive space or add user/service account permissions in the file path. Unless you are initially setting up an audit, typically permissions are not the issue. 

Read on for an example and how to resolve this issue.

Comments closed