Sql-Server

As we all know backing up to Azure Storage has been easy for some years now, but only since SQL Server 2022 have Microsoft supported backing up to S3. This includes third party providers that implement the S3 bucket protocol. Why do this? To keep backups safe and secure, best practise dictates that backup files should be off-site. Rather than backing up locally to a volume and then copying the file to S3, money can be saved by not having to provision space for local volumes. It is common in AWS to use an FSx volume for backups, or even a mounted EBS volume, but these costs can be removed by backing up directly to an S3 bucket. ...

The Problem Recently, I was working on a personal project where I needed to export data from my Azure SQL Database into a JSON file roughly every hour. This JSON file would then be used by a static website hosted on Azure Blob Storage. I wanted a secure way to do this without maintaining passwords, so I decided to use Managed Identity to connect my Logic App to the Azure SQL Database. ...

The problem Recently, I was performance tuning and enhancing the reliability of several SQL Server 2019 and 2022 databases for a client and discovered NOLOCK hints everywhere. Why are these bad, they seem to improve performance and remove blocking for most queries? 1. Dirty reads This allows data to be read that is currently being modified by another query leading to inconsistent results. If a row that has been read gets rolled back because of an error like a deadlock, or network issue, the data never existed! ...

Overloaded server I recently worked on a SQL Server 2019 CU27 on-premises server that was running at 100% CPU sustained several times during business hours and also during the night running batch processes. This was impacting users, who were complaining. The VM was running in vmware which supports hot cpu add functionality. Hot add The number of CPUs was doubled from 8 to 16 to deal with the customer workload. In order for SQL Server to use these CPUs in SQL Server 2019, the RECONFIGURE command must be run. When that was run all looked fine. ...

The Problem The incident Recently, a system I am working on experienced a noticeable slow response time for users due to high PAGEIOLATCH_SH waits. These waits are SQL Server waiting on a latch to read data from disk into memory. At the time we noticed that the distribution agent cleanup task was running for over an hour and checking the latency on the disks revealed that disk read time was peaking at 400ms, when it should be below 10ms. ...

A couple of months ago I wrote about a race condition in a SQL IaaS VM using the Azure sqlvm resource. I have been working with Microsoft and have come to a better resolution than placing tempdb on a separate remote drive as mentioned in that article. Suggestion Microsoft suggested to change the startup type of the SQL Server service to Automatic (Delayed Start). I have now put this into the bicep template for both SQL Server and SQL Server Agent service. ...

I came across a situation this week where the msdb database had grown to a large amount (130 GB). This is the largest I’ve ever seen msdb and is a result of no scheduled maintenance of the backup history for several years. msdb.dbo.sp_delete_backuphistory Why not just run msdb.dbo.sp_delete_backuphistory I hear you say? Well, when the history tables have over 100 million rows each and the database files are on the C drive, running this proc will require huge amounts of transaction log space. Each delete statement will be cleaning out millions of rows per table in a single transaction. ...

I’ve been having problems deploying a Sql Virtual Machine in Azure lately and decided to perform some tests to get to the bottom of the issue. I’d like to share some strange behaviour. Problem If I deploy a new SQL VM using bicep, it deploys fine. If however, I redeploy the same VM the deployment fails with this error: Ext_AutomatedBackupError * code: **Ext_AutomatedBackupError** * Error: **Execution Timeout Expired**. The timeout period elapsed prior to completion of the operation or the server is not responding.;System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out. In the SQL Server ERRORLOG on the VM: ...

Even though Microsoft offer Azure Sql Database and Azure Sql Managed Instance, there are occasions when SQL Server in a VM is required. The SqlIaaS Azure Resource If you’ve read my posts before you will know that I deploy resources to the cloud using infrastructure-as-code, with my preferred language being bicep. As a lazy developer, I like to use built-in offerings and PaaS wherever possible. For SQL server on VMs in Azure, the resource Microsoft.SqlVirtualMachine/sqlVirtualMachines is great. ...

I had an interesting issue today when a colleague was running a long running IO intensive query. I ran sp_who2 to view the activity and saw that the query was running multi-threaded, but was blocked by a SPID of -5. Yes, that’s SPID minus five, or negative five. (putting this here so that google searches maybe work?). blocking SPID -5 Google not working I tried to use Google to get information on this but it seems that Google doesn’t allow you to search for negative numbers. So searching for SPID -5 doesn’t show any decent information to understand what’s going on. ...