Press "Enter" to skip to content

Category: Error Handling

Generating a Multi-Aggregate Pivot in Spark

Published 2024-09-13 by Kevin Feasel

Richard Swinbank troubleshoots an issue:

I’m using a stream watermark to handle late arriving data – basically1) my watermark enables the stream to accept data arriving up to 10 seconds late …and that’s where the problem shows up.

When I run this streaming query – in Azure Databricks I can do this simply with display(df_pivot) – I receive the error:

AnalysisException: Detected pattern of possible ‘correctness’ issue due to global watermark. The query contains stateful operation which can emit rows older than the current watermark plus allowed late record delay, which are “late rows” in downstream stateful operations and these rows can be discarded. Please refer the programming guide doc for more details. If you understand the possible risk of correctness issue and still need to run the query, you can disable this check by setting the config `spark.sql.streaming.statefulOperator.checkCorrectness.enabled` to false.

Read on to learn more about the scenario, the issue, and the solution.

Comments closed

mssql-tools 18 and Two Common Errors

Published 2024-09-10 by Kevin Feasel

Vlad Drumea covers a pair of errors you might run into with mssql-tools version 18:

In this post I cover the 0A000086 and “command not found” errors that you might encounter with the new version of SQL Server command-line tools, namely sqlcmd and bcp, for Linux.

While the latest version of SQL Server command-line tools, based on Microsoft ODBC 18, brings improvements, it also brings some gotchas that can break your automations.

Read on to learn more about each.

Comments closed

The Brokenness of TABLESAMPLE

Published 2024-09-10 by Kevin Feasel

Paul White walks us through some issues:

Initial testing went well, which was a pleasant surprise. Soon enough though, errors started to appear in the tool’s output. That’s not entirely unexpected since ensuring consistent results under high concurrency tends to expose all sorts of niggly edge cases. It’s still an annoyance because debugging edge cases in trigger code can be tricky and laborious.

What was a surprise though was the nature of the error messages.

Read on for the full story. Paul has also created a feedback issue covering a problem with the function.

Comments closed

SQL Server Msg 3023 during DBCC SHRINKFILE

Published 2024-09-09 by Kevin Feasel

Tom Collins gets an error:

Question : Executing the following  Database shrinkfile activity and getting the error message

use myDatabase
DBCC SHRINKFILE (N’myDatabase_log’ , 0, TRUNCATEONLY)

Msg 3023, Level 16, State 2, Line 4
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

Read on for the answer.

Comments closed

Automating Unforcing of Failed Query Store Forced Plans

Published 2024-09-03 by Kevin Feasel

Kendra Little has a script for us:

tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic “failed” forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.

This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.

Read on to learn more about what might cause these failure to occur and what you can do about them.

Comments closed

Databricks Notebook Package Installation and Variables

Published 2024-08-26 by Kevin Feasel

Chen Hirsh diagnoses a problem:

A friend called to ask for my help with a weird issue. In a Databricks notebook using Python, he declares and assigns a variable in the first cell. Something like that:

my_var = 1

He then runs the rest of the notebook, and somewhere along the way, tries to use this variable, and gets this message:

NameError: name 'my_var' is not defined

Going back to cell 1, and checking the value of my_var, he gets the same error.

Read on for the root cause of the issue, as well as a pair of helpful tips from Chen.

Comments closed

Reviewing the SQL Server Error Log

Published 2024-08-23 by Kevin Feasel

Jim Evans digs into the logs:

In SQL Server there are two primary sets of error logs. One for the database engine and a second for SQL Server Agent. Reviewing these logs is routine for Database Administrators and sometimes Developers when troubleshooting issues. What are the different ways to view these error logs? Are there different scenarios when you would use one view other another? Do any other error logs exist that SQL Server Professionals should review?

Read on for three ways to do this, including one outside of SQL Server itself.

Comments closed

Error 1119 on Database Shrink

Published 2024-08-21 by Kevin Feasel

Kendra Little troubleshoots an error:

At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:

Msg 1119, Level 16, State 1, Line 11 Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using the object that this IAM page belongs to. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error.

Click through to see how Kendra was able to get around this issue.

Comments closed

Troubleshooting Non-Editable Power Query Parameters in Microsoft Fabric

Published 2024-08-20 by Kevin Feasel

Soheil Bakhshi digs into a problem:

Power Query is a powerful tool within the Microsoft Fabric environment, enabling users to manage data sources and transform data efficiently. However, a common issue you may face is that after publishing the Semantic Model, the Power Query parameters either do not appear or are greyed out, making them non-editable. In this post and its accompanying YouTube video, I’ll walk you through the steps to diagnose and fix these problems, ensuring that your parameters work as expected in your published semantic models.

Click through for the video and a pair of common reasons.

Comments closed

Concurrent Index Creation in Postgres

Published 2024-08-13 by Kevin Feasel

Shayon Mukherjee recommends against a particular technique:

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.

Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.

Comments closed