Press "Enter" to skip to content

Category: Administration

Tracking Online Index Rebuild Progress

Published 2026-09-15 by Kevin Feasel

Rich Benner doesn’t take 0% for an answer:

Ever had a situation where you’re rebuilding a large index but when you check sp_whoisactive, you see 0% complete … and you know the system is just lying to you?

We recently had a scenario where we were rebuilding a large index for a customer over a long weekend. We were monitoring closely, as this has caused production issues in the past. After 2.5 hours, we did not see any progress on the rebuild.

Read on to see what you can use to estimate the progress.

Comments closed

The Importance of Application Names

Published 2026-09-10 by Kevin Feasel

Brent Ozar explains how powerful application names can be:

When you run monitoring queries like sp_BlitzWho and sp_WhoIsActive, you wanna see the program names that are running the queries. It’s super useful when you’ve got multiple apps running on the same servers, or when you’ve got apps scattered across end user computers.

If you don’t set the program names, you’ll either end up with empty strings, or your dev tool’s default name, like Core .Net SqlClient Data Provider, which doesn’t tell you jack.

And, once you do have application names established for everything, you can also set up Resource Governor to use those application names for the purpose of routing to Resource Governor pools.

Comments closed

To Make a DBA

Published 2026-09-10 by Kevin Feasel

Andy Brownsword tells his origin story:

I enjoy a good war story.

But no, this isn’t the time Azure blindsided me, when service accounts were locked, or when I left a transaction open and halted a production system (easily fixed by closing SSMS 😅). Mine is a pivotal personal moment.

Andy humbly leaves out the part where he was hand-to-hand kung fu fighting a series of ninjas while doing all of this, but you can just assume that in there.

Comments closed

DBAs as Cost Centers

Published 2026-09-08 by Kevin Feasel

Warwick Rudd lays out the bitter truth:

That has been the problem for as long as there have been database administrators, and I don’t think we’ve ever really solved it.

Here’s what I mean. A DBA doing the job properly produces nothing you can see. The environment is up. The queries return. Nobody rings anybody at two in the morning. And because nothing appears to be happening, the people looking after it start to look like a line of cost with nothing attached to it.

In the traditional sense of how businesses think about this, they’re right. Nothing a DBA does makes money. What a DBA does is save money, which is a much harder thing to put in front of a board.

Warwick’s post moves in the direction of managed services. I’d move in the direction of understanding and vocalizing the benefits you bring to the table as a DBA.

Comments closed

When Ints Overflow

Published 2026-09-08 by Kevin Feasel

Aaron Bertrand hits an outage in both directions:

The Stack Overflow database has a long lineage, and a lot more warts than what’s exposed in Stack Exchange Data Explorer (SEDE). Many of the core tables were created a decade before I joined the staff in 2021, and they grew far larger than could have been envisioned back then.

One of those tables is UserHistory. This table uses an identity column as the clustered index and primary key, and records all kinds of information about each user’s activity on the site. From changing your profile picture, to earning a new privilege, to changing a preference, to something as simple as logging in or out. Each event generates at least one new row in UserHistory. At peak popularity, this table would grow quite quickly and, as the application became more complex, more and more categories of activity and state would get written there.

Click through for the story of an outage, and then another outage with a very similar shape.

Comments closed

Dealing with Outages

Published 2026-09-08 by Kevin Feasel

Jeff Taylor tells a pair of stories:

It was a normal day at the start, checking on the servers, responding to email, then all of a sudden the office went dark and silent…we had just lost power!

Everyone started stirring and then getting up, checking that our phone system was on backup power, and someone called the power company to report it and get a status of when the power would be back on.

Extreme heat, smoke, and servers are not a great combination.

Comments closed

Tracking SQL Server Login Failures

Published 2026-09-04 by Kevin Feasel

Ed Pollack builds some infrastructure:

Failed logins are one of the clearest early-warning signs of trouble on a SQL Server – whether that’s a misconfigured connection string, an expired password, or an actual unauthorized access attempt. Yet, by default, SQL Server won’t proactively tell you when they happen; you have to go looking.

This guide walks through how to pull login failure data using sys.xp_readerrorlog, filter it by time and error type, parse it into readable columns, aggregate repeat offenders, and automatically email a summary report — turning a passive log file into an active security and troubleshooting tool.

Click through for the process and scripts.

Comments closed

DDL Modifications and Change Data Capture

Published 2026-08-27 by Kevin Feasel

Erik Darling has a new video:

So I’ve had to deal with this with some clients recently, and the problem with CDC, of course, is that if you change, add, drop columns from CDC tables, or tables that are covered by CDC, rather, the current change capture table does not reflect those changes. You have to do some work to figure it out. What I’ve got in this video is I’m just going to, a script that I can walk through, I can hit F5 on it.

Click through for the script, information on capture instances, and more.

Comments closed

Moving from a Named Instance to a Default Instance

Published 2026-08-27 by Kevin Feasel

Brian Kelley makes a move:

I have a SQL Server named instance that is used by various resources. There may even be reports and other artifacts that access the named instance which we don’t know about. Having a named instance means in a recovery situation we must have a SQL Server installed as a named instance with the same name. This impairs our recoverability as well as our ability to upgrade SQL Server versions because we must retain that named instance. Is there a path to migrate to a default instance without potentially breaking things?

Click through to see how.

Comments closed

Waiting for GOdot, SSMS Edition

Published 2026-08-25 by Kevin Feasel

Tom Zika waits to pass GO:

I deployed a schema change to 30 servers using my deploy-at-low-priority script via SSMS multi-server query. Some of these servers were small with almost no activity, so I expected them to finish within seconds. I opened another multi-server connection to check on progress and none of the small servers showed any sign of having completed. Then all 30 finished at the same time.

Read on to see what happened and why.

Comments closed