Maintenance

Hello everyone, In this blog post, I want to share with you a problem that I faced this week with moving a VM from one vnet to another in Azure. It was not as easy as I expected and it required some downtime. The problem I had a VM that needed to move to another vnet as part of organisational change. I thought it would be a simple task of creating a new network interface card (NIC) in the target vnet and attaching it to the VM. However, it seems that it’s not possible to create a NIC in another Vnet and attach it to a VM attached to a different vnet. Also, as you may know a VM must have at least one NIC attached at all times which must be connected to a vnet. ...

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. ...

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. The whole point of CHECKDB is to notify us that something is wrong but also to do something if the database becomes corrupt in some way. They are very IO intensive operations which can impact user queries on the server. ...

Maintenance Window now in Preview (at the time of writing) On 2nd March 2021, Microsoft announced a long-awaited feature, at least by me, to give choice over when OS and SQL patching occurs behind-the-scenes in Azure SQL Managed Instance. Before this feature, Microsoft would patch the Virtual Cluster supporting Managed Instances outside of regular business hours in the region your Managed Instance is located. You now get two additional options: Weekday window, 10PM to 6AM local time Monday – Thursday Weekend window, 10PM to 6AM local time Friday - Sunday System default: ...