Press "Enter" to skip to content

Category: T-SQL Tuesday

Self-Healing in SQL Agent Jobs

Published 2025-05-14 by Kevin Feasel

Rob Douglas doesn’t want to click the button a second time:

Recently, someone made an off the cuff comment about using TSQL Tasks in Maintenance Plans to handle more complex logic that SQL Agent handled out of the box. I was briefly excited by the prospect of building improved logic flows directly into SQL Server Maintenance Plans. This months Andy Levy hosts TSQL Tuesday and asks us about how we handle SQL Agent Jobs and this seemed like a great opportunity for me to share a story about how I wasted an afternoon testing a few components I have a passing knowledge with, attempting to implement auto-healing and conditional logic flow, only to snap out of it a few hours later when I realized that I was trying to solve a problem that someone else cracked nearly 2 decades ago.

Click through for the example. Some people might think of this kind of spelunking as a waste of time. My counter-argument is that it is better to know three ways of doing a thing versus one, as inevitably, you’ll run into the situation in which the one way is either not workable or is such a bad option that it’s painful to implement.

Comments closed

Visualizing SQL Agent Job History

Published 2025-05-14 by Kevin Feasel

Andy Levy wants a picture or at least a thousand words:

If you don’t have a monitoring suite watching SQL Agent, or you want to get a bigger picture view of when and how your Agent jobs are running, dbatools can help you shortcut getting this information. Get-DbaAgentJobHistory does exactly what its name suggests – it fetches the execution history for one or more Agent jobs on one or more SQL Server instances. Thing is…it can produce a lot of output if you’re not careful.

Read on to see how you can use this to generate a visual indicator of when your jobs are running and how they fare.

Comments closed

T-SQL Tuesday 184 Round-Up

Published 2025-03-20 by Kevin Feasel

Deborah Melkin casts a wide net:

There were a lot of themes that I noticed throughout everyone’s posts. First were the number of people who mentioned that mentoring doesn’t have to be formal or even a 1:1 relationship. Mentoring isn’t just for adults and careers, but for the next generation too. Mentoring has helped their careers or become part of a core tenant in their company and how they run their business. It’s a place to grow our community, and not just for those who look like us. We all talked about how we have grown from mentoring, not just as mentees but as mentors.

Click through for a dozen-and-a-half responses to the T-SQL Tuesday call.

Comments closed

The Pain of Permissions

Published 2025-02-18 by Kevin Feasel

Hugo Kornelis talks about pain:

But I have been in contracts where I was the only employee able to spell SQL, and hence all other database tasks also fell in my lap. Including permissions.

And yes. I have been in projects where the idea was to investigate all current permissions, check which are and which are not needed, and then correct everything that was wrong.

We never got past stage 1. Even in a moderate sized company, with moderate sized database applications, getting a full overview of who has which permission was sheer hell.

The best I’ve ever been able to do is execute as each user and then query sys.fn_my_permissions. Otherwise, you won’t know the full scope of a user’s permissions because there are group permissions that querying other DMVs as a sysadmin won’t give you. And frankly, even this isn’t a foolproof operation.

Comments closed

Creating Logins and Users via dbatools

Published 2025-02-17 by Kevin Feasel

Chad Callihan creates a new user:

I can’t remember where I heard the analogy, but think of a SQL Server Login as the key to a hotel. While a Login will get you in the hotel, you need a room-specific key (User) to access specific rooms (or databases) in that hotel.

When it comes to creating new logins and users, dbatools can help make it a more manageable process. This is especially helpful when you’re deploying the same login and/or user to multiple servers at a time.

That’s a nice analogy, and Chad follows it up with a pair of dbatools cmdlets you may find helpful.

Comments closed

T-SQL Tuesday 182 Round-Up

Published 2025-01-16 by Kevin Feasel

Rob Farley gives us a roundup:

I was the host for T-SQL Tuesday this month, inviting people to write about integrity. And because I don’t believe in just providing a list of posts (although I’ve also done that at the end of the post), I have a completely fabricated story for you. And no, this wasn’t created using AI. I have more integrity than that (see what I did there?).

Read on for Rob’s summary and all of the links.

Comments closed

Automating DBCC CHECKDB on Full Backups

Published 2025-01-15 by Kevin Feasel

Shane O’Neill riffs off of a script:

Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.

At least this way we can test the backups we take can be restored, as well.

But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…

Click through for a script that Shane built off of a Madeira Data Solutions script that Eitan Blumin put together.

Comments closed

Thoughts on Data Integrity

Published 2025-01-15 by Kevin Feasel

Deborah Melkin shares some thoughts:

The first way to think of data integrity is a very small and literal interpretation. This is making sure that our data in the database is good. In many ways, these are easy to enforce – you add constraints. Primary Keys ensure that you know what makes each row unique. Unique constraints represent what would make each record unique if the primary key constraint, which is often a surrogate key these days, didn’t exist or offer different options. 

Read on for more about database design, default constraints, and a dive into data modeling.

Comments closed

It’s Probably Not Data Corruption on Disk

Published 2025-01-15 by Kevin Feasel

Andy Yun talks storage:

I cannot tell you how many times I’ve encountered scenarios where “this data looks wrong.” Well… can one ensure that it is being retrieved and displayed correctly from the storage media that it resides on in the first place? Are you viewing/validating the data in question correctly? Whatever client/method you are using to review your data – that is suspect and its integrity is in question.

It is technically possible for bits to flip, but that’s also why we have checksums on disk. I’m sure there are people who have experienced storage corruption that changed just enough to cause problems but not enough to be noticeable, but Andy is right on the money.

Comments closed