Category: Bugs

Query Store Deadlock When Creating Databases

Published 2018-03-21 by Kevin Feasel

Andy Mallon ran into a weird issue with Query Store:

I tried the configuration a couple of times just to make sure it wasn’t a one-off problem. I installed the latest Cumulative Update (CU). I made sure nothing else was connected to the instance. I rebooted my machine. I restarted services. I banged my head against the wall. I asked a friend if I was insane or stupid. After confirming that I was both, my friend Aaron Bertrand (blog|twitter) confirmed it wasn’t a problem for him.

I discovered I could reproduce the problem simply by running the same simple statement that SSRS used when creating the ReportServer database. SSRS uses a non-standard collation, and specifying that collation seems to be the difference in causing the deadlock. Then I discovered that specifying ANY non-standard collation was causing the deadlock. This had nothing to do with SSRS, and everything to do with non-default collations.

Vote for his User Voice item too.

Comments closed

Invalid Dates And Power BI

Published 2018-03-09 by Kevin Feasel

Melissa Coates notes a discrepancy between the Desktop and Service versions of Power BI:

Last week I got involved with a customer issue. A refresh of the data imported to a PBIX always works in Power BI Desktop, but the refresh operation intermittently fails in the Power BI Service. Their workaround had been to refresh the PBIX in Desktop and re-upload the file to the Service. This post is about finding and fixing the root cause of the issue – this is as of March 2018, so this behavior may very well change in the future.

Turns out, the source of the problem was that the customer’s Open Orders table can contain invalid dates – not all rows, just some rows. Since Open Orders data can fluctuate, that explains why it presented as an intermittent refresh issue. Here’s a simple mockup that shows one row which contains an invalid date:

At this point, we have two open questions:
(1) What is causing the refresh error?
(2) Why is the refresh behavior different in the Service than the Desktop tool?

Read on for the explanation of the difference, as well as a fix to prevent refresh errors due to invalid dates.

Comments closed

Read-Only Databases And Single-User Mode

Published 2018-02-26 by Kevin Feasel

David Fowler notes an old bug in SQL Server 2012 and 2014 which bit him recently:

Here’s a strange one that I’ve recently come across. I had a customer report that their log shipping restore jobs were chock a block of errors. Now, the logs seem to have been restoring just fine but before every restore attempt, the job is reporting the error,

Error: Failed to update database “DATABASE NAME” because the database is read-only.

Unfortunately I haven’t got any direct access to the server but their logshipping is setup to disconnect users before and leave the database in standby after. After a bit of to-ing and fro-ing, I asked the customer to send me a trace file covering the period that the restore job ran.

Read on for the details and keep those servers patched.

Comments closed

Beware Multi-Assignment dplyr::mutate() Statements

Published 2018-01-23 by Kevin Feasel

John Mount hits on an issue when using dplyr backed by a database in R:

Notice the above gives an incorrect result: all of the x_i columns are identical, and all of the y_i columns are identical. I am not saying the above code is in any way desirable (though something like it does arise naturally in certain test designs). If this is truly “incorrect dplyr code” we should have seen an error or exception. Unless you can be certain you have no code like that in a database backed dplyr project: you can not be certain you have not run into the problem producing silent data and result corruption.

The issue is: dplyr on databases does not seem to have strong enough order of assignment statement execution guarantees. The running counter “delta” is taking only one value for the entire lifetime of the dplyr::mutate() statement (which is clearly not what the user would want).

Read on for a couple of suggested solutions.

Comments closed

ML Services Can Fill The Plan Cache

Published 2018-01-04 by Kevin Feasel

I have a post talking about a bug in SQL Server:

For now, the workaround I have is to restart the SQL Server service occasionally. You can see that I have done it twice in the above screenshot. Our application is resilient to short database downtimes, so this isn’t a bad workaround for us; it’s just a little bit of an annoyance.

One thing to keep in mind if you are in this scenario is that if you are running ML Services hundreds of thousands of times a day, your ExtensibilityData folders might have a lot of cruft which may prevent the Launchpad service from starting as expected. I’ve had to delete all folders in \MSSQL14.MSSQLSERVER\MSSQL\ExtensibilityData\MSSQLSERVER01 after stopping the SQL Server service and before restarting it. The Launchpad service automatically does this, but if you have a huge number of folders in there, the service can time out trying to delete all of them. In my experience at least, the other folders didn’t have enough sub-folders inside to make it worth deleting, but that may just be an artifact of how we use ML Services.

It’s very unlikely to affect most shops, as we only notice it after running sp_execute_external_script millions of times, and that’s pretty abnormal behavior.

Comments closed

SQL Server Feedback In A Post-Connect World

Published 2017-12-20 by Kevin Feasel

Koen Verbeeck touts the new SQL Server feedback site:

After years of having to deal with Connect – the feedback platform of Microsoft – it is announced a successor has been found: feedback.azure.com. It’s not all about Azure, the link goes to the relevant portion of SQL Server. I’m glad for this change, as Connect could sometimes be a little … quirky. Especially the search function didn’t work properly. The new feedback site is based on UserVoice and it’s really easy to submit feedback. People submitting ideas for Power BI will be very familiar with the format. There are a couple of drawbacks:

You cannot specify many details (none to be exact, or you have to list them in the descriptions). OS version, SQL Server version, bitness, et cetera. On the other hand it makes the process of entering feedback a lot faster.
You cannot mark a feedback item as private so that only Microsoft can see it. This means it’s not exactly the place to dump your production data to show how a bug is bugging you (haha).

I’m not sure how much of an improvement this is, but at least it does serve the Power BI team well.

Comments closed

SQL Server 2017 CU2 Compatibility Mode Bug

Published 2017-12-06 by Kevin Feasel

Tracy Boggiano has found a bug in SQL Server 2017:

This will be a very short blog post to make you aware of a bug in CU2 for all of those who I know have eagerly installed the newest CU for 2017. A small bug I have found is that it changes your compatibility mode on the msdb database to 130. All our servers were set to 140 and our nice server policy check alerts fired off and sent me 58 pages the day after I installed it in my development environment. Well, I double checked before installing on QA today and sure enough, it changed it from 140 to 130. So have your code ready to change it back after you install.

Click through for a script to fix the compatibility level.

Comments closed

Bug In Older Versions Of SQL Server 2012 & 2014

Published 2017-12-06 by Kevin Feasel

Paul Randal explains a bug in versions of SQL Server 2012 and 2014:

There hasn’t been a case of it failing to reserve enough space until SQL Server 2012, when a bug was introduced. That bug was discovered by someone I was working with in 2015 (which shows just how rare the circumstances are), and at the time it was thought that the bug was confined to the log of tempdb filling up, rollback failing, and the server shutting down.

However, just last week I was contacted by someone running SQL Server 2012 SP3 who’d seen similar symptoms but for a user database this time, and the user database went into recovery.

Read on for details and make sure those SQL Servers are patched.

Comments closed

Love Your Bugs

Published 2017-11-14 by Kevin Feasel

Allison Kaptur has an appreciation for strange bugs:

I love this bug because it shows that bitflips are a real thing that can happen, not just a theoretical concern. In fact, there are some domains where they’re more common than others. One such domain is if you’re getting requests from users with low-end or old hardware, which is true for a lot of laptops running Dropbox. Another domain with lots of bitflips is outer space – there’s no atmosphere in space to protect your memory from energetic particles and radiation, so bitflips are pretty common.

You probably really care about correctness in space – your code might be keeping astronauts alive on the ISS, for example, but even if it’s not mission-critical, it’s hard to do software updates to space. If you really need your application to defend against bitflips, there are a variety of hardware & software approaches you can take, and there’s a very interesting talk by Katie Betchold about this.

This is an interesting approach, although I fear I may not have the temperament to love my bugs so much as despise their existence.

Comments closed

Locale Problems With SSAS

Published 2017-11-01 by Kevin Feasel

Koen Verbeeck diagnoses an awful Analysis Services error:

This really didn’t make any sense. However, in one of the Discover Begin/End events, the same number appeared again: 8192 (this time explicitly marked as locale identifier). Hmmm, I had problems with weird locales before. I dug into my system, and yes, the English (Belgium) locale was lingering around. I removed it from my system and lo and behold, I could log into SSAS with SSMS again. Morale of the story: if you get weird errors, make sure you have a normal locale on your machine because apparently the SQL Server client tools go bonkers.

Worth reading the whole thing. And also maybe just using en-US for all locales; at least that one gets tested…

Comments closed