A Fine Slice Of SQL Server
Jose Manuel Jurado Diaz diagnoses an error:
Today, we worked on a service request that our customer got the following error message: BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation [SQLSTATE 42000] (Error 41937) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013), running a manual backup.
Click through to learn when you might see this error and what you can do about it.
Comments closedGrant Fritchey observes the observers:
Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I’m taking a look at what happens on Azure SQL Database. I’m using the same toolset again, if for no other reason that I’m consistent in my approach. So it’s basically just rpc_completed & sql_batch_completed on the database in question. Let’s check out the results.
Here’s the prior post, in case you missed it like I did.
Comments closedNathan Schoenack explains an error:
At end of October 2022 we saw an issue where a customer using PolyBase external query to Azure Storage started seeing queries fail with the following error:
Msg 7320, Level 16, State 110, Line 2
Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. EXTERNAL TABLE access failed due to internal error: ‘Java exception raised on call to HdfsBridge_IsDirExist: Error [com.microsoft.azure.storage.StorageException: The server encountered an unknown failure: ]occurred while accessing external file.’
Prior to this, everything was working fine; the customer made no changes to SQL Server or Azure Storage.
I guess it doesn’t matter so much unless you’re interested in getting support, but Java SE 7 is no longer supported. Java SE 8 is still in support and JRE 8 remains the best version for PolyBase integration in my experience.
Comments closedRyan Booz divvies up ownership rights:
In the first security article, PostgreSQL Basics: Roles and Privileges, I discussed how roles (users and groups) are created and managed in PostgreSQL Depending on your background with permissions, particularly in other database products, some of those nuances in how permissions work may have been surprising.
Understanding how roles and privileges work in Postgres is key to understanding the next, and often more confusing part of security, object ownership. Although there are many privileges that can be assigned in Postgres, object ownership comes with a specific level of privilege that cannot be transferred to other roles. Understanding the implications of this is essential to the management of your database schema, and access to the objects it contains, over time.
Click through to learn more about how Postgres handles object ownership and defines who gets access to which objects.
Comments closedQuestion: SQL Server won’t start , so checked Event Viewer and getting the following message.
Server TCP provider failed to listen on [ ‘any’ <ipv4> 50010]. Tcp port is already in use.
How can I check if the port is already in use and which other process or service has locked the port and therefore not allowing SQL Server to start on designated port ?
Read on to see how you can use Powershell to find the answer on Windows.
Comments closedJared Poche digs further into Halloween protection:
I find myself talking about the Halloween Problem a lot and wanted to fill in some more details on the subject. In short, the Halloween Problem is a case where an INSERT\UPDATE\DELETE\MERGE operates on a row more than once, or tries to and fails. In the first recorded case, an UPDATE changed multiple rows in the table more than once.
So let’s take a look at an example using a publicly available database, WideWorldImporters.
Read on for a case of Jared starting from the known and moving into the unknown.
Comments closedSrinivas Kandibanda and Harvey Mora have announcements:
The 1st cumulative update release for SQL Server 2022 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.
Both of these came out several months later than expected, though with the big GDR that dropped yesterday, it seems like that cleared up the logjam.
Comments closedHarvey Mora notes that Microsoft has been busy:
The Security Update for SQL Server 2022 RTM GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package includes the new security fixes detailed in the KB Article.
This link is specifically for SQL Server 2022 but there are other posts for 2019, 2017, 2016, and 2014. This includes fixes to several security problems and is something you should install post-haste. Because this is a GDR, you’re also liable to find it in Windows Update if your update admins haven’t shut that channel off already.
Comments closedBy sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.
Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.”
Read on to learn what had Andy so excited.
Comments closedGrant Fritchey does some housekeeping:
The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:
Even with that limit, there are still several options available to you for when to clean up old Query Store data, whatever the definition of “old” (i.e., time-based or at the user’s whim).
Comments closed