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.

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.

Question Where should variables be stored for deploying infrastructure-as-code in Azure DevOps pipelines to Azure? Resources need to be deployed to different regions and environments, and attributes for these need to be stored somewhere. Options Six options spring to mind. Parameter files. Seems like the obvious choice. Template file defaults in azuredeploy.json or main.bicep Azure DevOps Library variable groups. Seems good, a single place Azure Pipelines variables in individual pipelines Configuration file within the project source code Runtime parameters for setting values at run time Let’s go through each option and then discuss my preferred approach.

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.

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.

As a standard practise for IaaS and on-premises SQL Servers, I usually like to install Ola Hallengren’s maintenance scripts. Why invent the wheel when we already have a super-shiny one? These scripts include integrity checks, which run DBCC CHECKDB. Is DBCC CHECKDB needed anymore? It depends. 😉 It comes down to a cost/benefit analysis. I have been working with Sql Managed Instances quite closely over the past couple of years and wondered whether DBCC CHECKDB needs to be run on these.

What if you could do a dry run of your PowerShell script when making a change to your production environment? If you’ve read my other posts, you will know I am a big advocate of infrastructure-as-code. Most of my work is done in the Microsoft Azure cloud, and although I encourage source controlling all assets within Azure, sometimes developers create things manually through the portal. Yes, I know! 🙄 Removing Azure Resources safely A scheduled drift report should alert developers or DevOps engineers that the production state is out-of-sync with the source controlled code.

One of my ADF pipelines started failing recently where a Generic REST API Linked Service gave the following error: 525: SSL handshake failed Digging deeper into the logs, I see a message from Cloudflare. 1 It appears that the SSL configuration used is not compatible with Cloudflare. This could happen for a several reasons, including no shared cipher suites. HTTP/3 QUIC There had been no code changes in ADF, but something on the source side had changed.

Windows Terminal was released in 2019 for Windows 10 on the Microsoft Store, and I only just found out about it. Not sure why I’ve been living under a rock all this time, but I’m absolutely loving this app. I found out about it after playing around with the GA release of Windows 11 last week and noticed that Windows Terminal was installed as part of the default build of the Windows 11 Pro edition.