Press "Enter" to skip to content

Category: Administration

Capturing Deadlocks with the system_health Extended Event

Published 2021-04-19 by Kevin Feasel

Jack Vamvas is hunting deadlocks:

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Click through for the script.

Comments closed

Using AD Authentication on Linux when Connecting to SQL Server

Published 2021-04-16 by Kevin Feasel

Daniel Hutmacher shares some hard-earned wisdom:

I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.

Also, different Linux distros and versions will behave differently, so your mileage will most likely vary.

For the purposes of this post, I’m on Red Hat Enterprise 8.3.

Note that this is using a Linux-based client, rather than talking about SQL Server on Linux.

Comments closed

Finding and Clearing the Recycle Bin with Powershell

Published 2021-04-12 by Kevin Feasel

Jack Vamvas answers a question:

I use Powershell extensively to manage SQL Server and the Windows OS. A common problem is to identify location of the  Windows Recycle Bin and clear the contents down – particuarly if there is a space issue. 

How can I locate the Windows Recycle Bin and clear it down?

Read on for the Powershell v5 solution as well as the solution which works for earlier versions.

Comments closed

IDENTITY Overflow in SSIS

Published 2021-04-08 by Kevin Feasel

Alex Stuart hits a weird error:

Conversion/overflow errors aren’t that unusual – normally a data flow broken by some unexpected data (“no, there’s no chance that field would ever have a character in it”), or perhaps a column hitting max size (“INT will be enough for years, like, 5 years. I’ll have left the company by then”)

But that wasn’t the case here – the package and user tables involved were checked by the dev team and there was no possible overflow. I’d checked system databases for maxed-out identity columns and found nothing. Heads were scratched.

Read on for the post-head-scratch answer.

Comments closed

Finding Spinlock Owners in Dump Files

Published 2021-04-08 by Kevin Feasel

Forrest McDaniel leaves us with a head injury:

Now, I may not be an expert, but sqlmin!Spinlock sounds like…a spinlock. This thread has been spinning for over a minute, never returning to a waiting state, because something else is holding the spinlock resource.

Thankfully, helpful friends alerted me to a blog that revealed the value of an acquired spinlock “is the Windows thread ID of the owner.” Meaning I might be able to find the cause.

Read the whole thing.

Comments closed

Automating Montoring

Published 2021-04-08 by Kevin Feasel

Thomas Williams has a multi-part series. Part 1 is a big picture primer:

It would be nice if every problem, now and in the future, was automatically monitored by an intuitive, cheap, flexible tool that only raised the alarm when there was a real problem, at the right time, to the right people.

However, in reality, monitoring is a game of whack-a-mole, built on an ad-hoc collection of tools and technologies, requiring know-how to interpret the often puzzling situations which may be high priority – or just background noise.

Part 2 continues the big picture look:

Monitoring is here to stay. There’s never enough disk space, memory, CPU, network throughput. If they ever existed, perfect conditions don’t persist, whether because of a downstream system failing, newly-discovered security issue, change in process, extraordinary amount of load etc. And, systems grow and change to meet new requirements, so yesterday’s monitoring may not meet tomorrow’s uptime goals.

Following on from part 1, here are some further “big picture” considerations for automated monitoring for the DBA:

Part 3 gets to ideas on instrumentation:

With some of the broader ideas out of the way, if there’s no monitoring in place and I was the “accidental”/”default” DBA, there are some metrics I’d definitely want to monitor. However, there’s a ton of real-time metrics I haven’t included below like CPU & memory use, number of current connections, disk I/O as at right now. They’re probably not good candidates for “roll-your-own”-type monitoring.

I consider “alerts” in the table below to include an e-mail, SMS or dashboard item that aims to trigger a response to fix. The collection frequency especially is just a guide – closer to real-time might be desirable, but the trade-off is impact on servers and connected apps.

Check out all three posts so far in the series.

Comments closed

Deploying an Azure Arc Enabled Data Services Controller

Published 2021-04-06 by Kevin Feasel

Chris Adkin continues a series:

If you have been following this series, you should have:

– a basic understanding of Terraform
– a Kubernetes cluster that you can connect to using kubectl
– a basic understanding of Kubernetes services
– a working metalLB load balancer
– a basic understanding of how storage works in the world of Kubernetes
– a Kubernetes storage solution in the form of PX Store, alternatively you can use any solution (for the purposes of this series) which supports persistent volumes, however to use the backup solution in part 9 of the series you will need to use something that supports CSI

From here, Chris explains the importance of the data controller and then deploys one.

Comments closed

What to Do if a Database Isn’t Synchronizing

Published 2021-04-02 by Kevin Feasel

Lee Markup has some advice:

I currently manage 5 AlwaysOn Availability Groups. Two are on SQL Server 2014 and overdue for an upgrade, while three of them are on SQL Server 2017.  From time to time I have run into a couple of different situations that I needed to troubleshoot and I want to tell you where to look and what to check on in these scenarios. I can’t possibly tell you about everything that could go wrong, but I can tell you about my experience with AlwaysOn Availability Groups and let you decide if that experience helps you or not.

Click through for more.

Comments closed