Press "Enter" to skip to content

Category: Administration

Preventing Unexpected Failover with AG Patching

Published 2020-05-27 by Kevin Feasel

Josh Darnell dealt with an issue with Availability Groups:

I had a 2-node availability group (AG) + fileshare witness system experience an unexpected failover recently.

The synchronous secondary was being patched, and when it came back up from a reboot, the current primary unexpectedly failed over. We weren’t done with all the patching on the secondary, so this caused a short outage, and we had to fail back to the original primary to finish the patching (which is of course another short interruption in availability).

The root cause was interesting enough that I decided to share the story here, and provide some general advice and debugging tips along the way.

Click through to understand why this happened and how you might be able to avoid the pain Josh experienced.

Comments closed

Blocking Inbound Connections to SQL Server

Published 2020-05-26 by Kevin Feasel

John Morehouse shows one quick way of preventing anybody else from connecting to your SQL Server instance:

We even tried to restart the instance into single user mode, however, every time that happened something else would take the connection before we could get into the instance.  We eventually restarted the SQL Server instance to normal operation so that we could investigate why we could not get a connection when in single user mode.

Turns out that with the production nature of the instance, the clients large farm of application servers was connecting to it faster than we could.   This was discovered by using sp_who2, however, you could use the DMV sys.dm_exec_connections to see what is connecting to the instance if you desired.  So, we needed a way to block incoming connections while not being evasive like shutting down the application servers or a large network change.

This is where the brilliance comes in.

Click through for the idea. This is the type of thing you keep in your back pocket in a real pinch, but hope never to need to use.

Comments closed

Ten Comments from a DBA

Published 2020-05-22 by Kevin Feasel

Kevin Chant spins the DBA archetype around:

2. So glad we test our backups.

Now this is something that every DBA should do, or at least persuade whoever is responsible for backups to do it.

Otherwise you may find yourself in a situation where a database is corrupt and a restore is not possible. Which means that you have to try and recover the database using other methods like the one here.

I can tell you from experience that this is definitely not the best situation to be in unless you enjoy working for over twenty-four hours straight. So, if your backups are not being tested at the moment then I highly recommend you change that.

And if you are a DBA who can’t say this, Kevin has some advice for each of the ten.

Comments closed

Getting Row Counts After Execution

Published 2020-05-19 by Kevin Feasel

Grant Fritchey gives us a few ways to find row counts after a query has finished:

But, if you really want to get picky, batches and procedures frequently have multiple statements. This means that the row count you’re getting isn’t for a particular table. If you want to get that, then you need to capture the statement level stuff, sp_statement_completed or sql_statement_completed, depending on if you’re looking at procedures or batches.

However, this is one of those “be careful what you wish for” moments. Because, if you capture all statements in a system, you’re going to have a very large amount of data to deal with. This is where filtering is your friend.

Click through for Grant’s full answer.

Comments closed

Handling Cross-Database Transactions

Published 2020-05-18 by Kevin Feasel

Michael J. Swart explains how cross-database transactions work on a single instance:

The transaction is touching two different databases. So it makes sense that the two actions should be atomic and durable together using the one single transaction.

However, databases implement durability and atomicity using their own transaction log. Each transaction log takes care of its own database. So from another point of view, it makes sense that these are two separate transactions.

Which is it? Two transaction or one transaction?

Click through to read the article, and then check the comments for a cautionary tale around database mirroring and cross-database transactions.

Comments closed

Solving Last Page Contention Problems

Published 2020-05-18 by Kevin Feasel

Erik Darling ponders several techniques for solving the last page contention problem in SQL Server:

If one dare go looking, a list of Microsoft Approved™ solutions for dealing with last page contention exists.

I’m going to walk through my experiences with them in different scenarios, so you get a better understanding of which ones work best, and which ones can be tough to implement.

Read the whole thing.

Comments closed

Removing and Refilling All Tables in a Database

Published 2020-05-18 by Kevin Feasel

Phil Factor has a couple T-SQL scripts for us to remove and reload a test database:

Copying a database using a BCP dataset from a DOS script or from PowerShell is fairly quick and trivial, but what if the data is already in another copy of the database on the same instance? Imagine you have a database build that is a copy of an existing database on the same instance and you want to run a ‘destructive’ test on the data, and do it over and over again.

Deleting all the data in a database should, you’d have thought, be trivial. Truncation doesn’t work because it doesn’t like to truncate tables with foreign keys, even if they are disabled. DELETE is safer but you will need to then reset any identity columns. Deleting large tables in one chunk isn’t scalable, so you have to do it in smaller chunks. I like to disable triggers while I do this as well. The only disadvantage comes if you have triggers that are supposed to be disabled because the simple code just re-enables them all.

This is, I think, one of the biggest selling points for containers where the database is built into the container image. You spin up a container based off of an image, perform your destructive testing, and destroy the container afterward. The next time you need to run these tests, spin up a new container. And if you need to change the data, modify the container. This introduces new challenges like how SQL Server on Linux has limitations which don’t exist on Windows, but for supported functionality, it’s a nice solution.

Comments closed

Finding the SQL Server Service Account with T-SQL

Published 2020-05-14 by Kevin Feasel

Andy Mallon gives us a way in SQL to find the service account used for SQL Server:

SQL Server 2005 introduced Dynamic Management Views(DMVs). Technically, there are both views & functions–DMVs & DMFs, but colloquially they’re often simply referred to as just “DMVs”. DMVs simply return information about the state of the server or database and can be used for monitoring and troubleshooting of server/database health & performance.

New DMVs are added all the time. Today, let’s take a look at one of those useful DMVs.

This won’t cover all of the services—for example, it does not include the PolyBase engine or data movement service accounts—but it will give you some of the most important ones.

Comments closed

Taking the Pulse of Your Azure VM

Published 2020-05-14 by Kevin Feasel

Mark Broadbent has put together a quick Powershell script to figure out what’s going on with an Azure VM:

I was recently given the task of identifying the state of an Azure VM so that an automation script using the az vm run-command invoke would not fail if the VM was down or under a reboot.

I initially thought the task would be really easy and a simple query of the VM state using Get-AzVM would provide us with a running state property of the VM, but as it happens the state is a little abstracted.

Click through to see how Mark solved the challenge.

Comments closed

Who and What is Using Your tempdb Space?

Published 2020-05-14 by Kevin Feasel

Guy Glantser helps us troubleshoot who is using tempdb space and for what purpose:

In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.

Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb.

The answer, of course, was Professor Plum in the Aviary with the eager spool.

Comments closed