Press "Enter" to skip to content

Category: Error Handling

In-Memory OLTP and HammerDB Setup Error

Erik Darling tracks down an error:

This a short post about a sort of quirky error message I got while trying to run the TPC-C load test against SQL Server’s in-memory tables.

Error in Virtual User1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The integer value XXXXXXXXXXXX is out of range.

Click through for the solution. Also, read down to the bottom of the post for a huge discount on Erik’s recorded training content. Erik is extremely knowledgeable and has a great way of explaining things, so take advantage of that knowledge.

Comments closed

FAIL_PAGE_ALLOCATION in SQL Server

Eric Cobb diagnoses an ugly issue:

I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load.

Here is a synopsis of what we were seeing:

This is a physical server and has 512GB of RAM installed. We have SQL Server 2016 installed, and fully patched (SP2 CU15 at this time). When load testing the server, it would start throwing errors such as:

“Failed allocate pages: FAIL_PAGE_ALLOCATION”

and

“There is insufficient system memory in resource pool ‘default’ to run this query.”

and

“Failed to allocate BUFs”

It would then write a memory dump to the log, and in most cases the server would become completely unresponsive and would have to be rebooted.

Read on to learn under what conditions this happens as well as the solution to the problem.

Comments closed

Query Processor Ran Out of Internal Resources

Andy Galbraith troubleshoots a problem:

 Unfortunately a common error in many of our client environments is this:

Error: 8623, Severity: 16, State: 1.

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.–There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed – a complex query.

Read on to see how to find this complex query, as well as a few examples of complex queries.

Comments closed

SQL Server Replication Requires Actual Server Names

Steve Stedman walks us through a pain point when using replication:

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name. (Replication.Utilities).

You might be thinking to yourself that you had a typo in the server name, but no, after checking the server name it matches what you can connect with.

When I’ve seen this error, often it will even tell me the server name it’s expecting, which then makes me ask why I have to type it in if it knows already.

Comments closed

Read Those Error Messages

Randolph West has a public service announcement:

My boss got upset with us one day on The Project From Hell. Tempers were frayed, tensions ran high, and other euphemisms were euphemisming. In short, we were all grumpy, and as expected on a project of this nature we kept making obvious mistakes and wasting our energy chasing our tails.

Obvious? Well, yes. It turns out that the answer to a particularly common issue we were running into was explained in the first line of the stack trace of the code that kept crashing. I’m not exaggerating for the sake of this story. The actual problem was explained in the first sentence of the error, in the very first line.

Microsoft products are fairly notorious about poorly-crafted error messages, but as Randolph mentions, often the solution is there if you take care to read the message.

Comments closed

GetAllTheErrorLogs: Combining Multiple Log Sources

Aaron Bertrand has a new project:

There’s a whole lot of grunt work in there that shouldn’t have to be done by a person. I don’t think you could automate the whole thing, because it is hard to predict exactly what events will be interesting and not, but I think 90% is achievable.

A colleague mentioned that they want to build something that would help, but even when that happens, that would up in proprietary code that only helps us. I saw Drew Furgiuele’s post on Building a Better Get-SQLErrorLog, and that gave me some ideas for what I would build. After reaching out to Drew, I created a GitHub repository with a working name of GetAllTheErrorLogs. Its elevator pitch is a simple sentence:

Powershell to assemble a timeline combining salient events from Windows Event Log, Failover Cluster log, and SQL Server errorlog.

Click through for the details as well as Aaron’s current progress.

Comments closed

Messy Code and Reasonable Expectations

Rachel by the Bay has a doozy of a story:

One day not so long ago, I was in a meeting listening to a team explain why their service had gone down and taken out a big chunk of a business. They were one of those things that has to exist and work in order for the actual “thing that makes money” to go. Think of delivering pizzas, connecting dog walkers with dogs who need to be walked, that kind of thing.

It turned out they had been crashing every time a request came through for a certain part of the country. That is, not all pizzas, dog walkers, or whatever it was were handled identically, so they had their own city or region configurations. Think of differences in pricing, taxes, features, or whatever. Trying to process a request for this one particular region had caused the entire process to die when it hit a new config that was “bad” somehow.

Read on for the story. This sounds like a boundary issue. Boundaries are messy and need thorough examination to handle as many possible points of failure as is reasonable. Taking seriously the point that it makes the code messy, the answer is not “Don’t do the checks,” but rather “Put the checks in a place where their messiness has a minimal impact on the rest of my beautiful code but still does the important work we need them to do.” Failing that, live with the mess and have a working process.

Comments closed

Dealing with Failing SQL Agent Jobs

Garry Bargsley has started a four-part series:

SQL Server Agent Jobs are one of the core features of SQL Server. Agent Jobs perform many actions, from maintenance tasks (server and database), data movement flows, replication, log shipping, data cleanup, health checks and many more. Since Agent Jobs are a critical component in a data organization, it is important to know when Agent Jobs do not succeed. There are several ways to accomplish the monitoring of failed Agent Jobs, from easy to more complex. This four part series is going to cover how you, the DBA, can be notified of failing Agent Jobs in your SQL Server environment.

Click through for part one, which is all about finding failed jobs and filtering down to relevant jobs using dbatools.

Comments closed