It’s been a while since I’ve needed to investigate a deadlock – before we moved to Azure Managed Instances. So, when I wanted to look at the details of a particular object, I used my old method of DBCC PAGE.
Imagine my surprise.
Continue reading
While looking at a system that I’m unfamiliar with, I queried the server setting for Maximum Degree of Parallelism (MAXDOP). It hadn’t been set to what I believed it should have been but I was then shown the MAXDOP setting against the database properties. I wasn’t aware that this existed, and it raised a few questions.
A recently created email profile had the wrong name, so needed to be changed. It isn’t possible to change it via the SSMS wizard and I struggled to find an article for such a thing. Then I turned to CoPilot, and it was wrong. To be fair to that software, there is a warning that the answers might be incorrect. However, the references that it provided gave me the threads that I could follow.
In a previous article I described the basics of setting up a SQL Server Audit. Having played around with this I have discovered ways to disable the audit without it being obvious. Therefore, I have written a small query that checks for such circumstances.
Having migrated a system to Azure Managed Instance (MI), there are several options that used to be available via SQL Studio Management Studio (SSMS) that are no longer. Amongst these is the ability to change the values for SQL Server Agent job history.
Continue reading
During my earlier years as a DBA, I was always warned away from DELETE CASCADE and had some of the pitfalls explained to me. But I’ve never actually examined it for myself. Having come across a database that uses it, I felt that I had to create a small test system, to understand it with some confidence
As part of the security standards required in my area there is a need to track several areas of database and SQL Server activity. Whilst using a much earlier version of SQL Server, we had a hand-crafted solution. Now we are using the latest version of SQL Server, it is time to use the tried and tested tools that Microsoft have provided.
Read more: SQL Audit – Basic Example Continue reading
Subtitle – Distributed AG, the Gift That Keeps on Giving
Unexpectedly, we had a connection timeout/failure between a couple of nodes of our Availability Group (AG). None of the documented causes of this (that we could find) were responsible for this, until somebody asked a very simple question….
Continue reading
Several of the instances in an Availability Group (AG) that I look after, have some of the Temp DB data files stored on the C: drive. This is generally not a good idea – certainly when you have another drive allocated for the TempDB. All TempDB files need to be in the correct location and moving them isn’t as difficult as some people would appear to believe.
Continue reading
Some time ago, I wrote an article that used the OUTPUT clause with the MERGE statement. It was a more complicated example of using OUTPUT, so I feel that more basic examples are required, to reflect a more common scenario I frequently encounter.
Continue reading
Notes on SQL 