Skip to content
Home » script

script

Hidden Risks in SQL Agent Job Logging: How Retry and Output Settings Can Compromise Your Logs

In many SQL Server environments, DBAs configure SQL Agent jobs with retry attempts (which is a smart move to handle transient issues gracefully). But with it comes a hidden pitfall that often goes unnoticed:

Retry-enabled job steps can silently overwrite logs.

And improper output file settings can cause runaway file growth.

The culprit? Improper use of append settings and missing time-based tokens in output file paths.

Read More »Hidden Risks in SQL Agent Job Logging: How Retry and Output Settings Can Compromise Your Logs

Understanding ‘ALTER TABLE SWITCH statement failed’ errors 4907, 4908, and 4912

Recently we’ve encountered an interesting use case for a customer.

They had a table with partitioning that they needed to be archived (with the help of said partitioning), but the ALTER TABLE SWITCH commands were failing with a peculiar error:

Error 4907, ‘ALTER TABLE SWITCH’ statement failed. The table ‘MyDB.dbo.PrtTable1’ has 4 partitions while index ‘IX1’ has 6 partitions.

This prompted some insightful research into some of the validations done by SQL Server behind the scenes when executing an ALTER TABLE SWITCH command.

Read More »Understanding ‘ALTER TABLE SWITCH statement failed’ errors 4907, 4908, and 4912

Drop All Redundant Indexes In Every Database All At Once

Redundant indexes in SQL Server are a phenomenon that is more common than I’d like to admit. I see it in quite a lot of shops. Meaning, that there’s still a significant target audience for this blog post!

This article published by Brent Ozar is very informative about redundant/duplicate indexes, what they mean, why they’re bad, and what should be done with them.

Also, a few years ago, Guy Glantser published a post about dropping redundant indexes. It’s very useful for finding all redundant indexes within all tables in a specific database.

But what both of these articles are missing – is the ability to easily generate Drop/Disable commands for these redundant indexes.

Read More »Drop All Redundant Indexes In Every Database All At Once

Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”. This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

Hopefully, with this blog post, I’ll provide you with an easy-to-understand step-by-step guide, which would help you resolve the issue the right way, without any workarounds.

Read More »Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step

Compare SQL Server Instance Properties

A few years ago, I created a couple of T-SQL scripts that can be used for comparing instance-level and database-level properties between two HA/DR replicas. Originally, this supported comparing only two servers. But recently, following a fan request, I upgraded the script to support an unlimited number of servers that you can compare to each other.

So, I figured, if one person found this useful, there must be more out there that would need this, right?

Read More »Compare SQL Server Instance Properties

Finding a use for Extended Properties in SQL Server

“Extended properties allow you to add custom properties to database objects”, so says the official Microsoft documentation. However, very few DBAs make use of them, if at all. This is actually a good thing, because indeed the scenarios in which this feature could be useful are relatively few and rare.

On the other hand, I also see DBAs essentially jumping through hoops to implement something, which could in fact very easily be implemented using Extended Properties.

What are these peculiar use cases? Let’s look at a few examples.

Read More »Finding a use for Extended Properties in SQL Server

One handy Powershell script template to rule them all

If you know me, you already know that I’m a huge fan of automation. Therefore, it’s natural to assume that I would dabble in Powershell at least once or twice or a few dozen, as a method to implement useful automations. After all, automation is pretty much the whole essence of Powershell in the first place.

As I used Powershell scripts more and more, I learned that there are a few things that are important to have whenever you use such scripts as part of automation (by “automation” I mean any sort of operation done “unattended”, i.e. without direct human interaction in real-time).

Read More »One handy Powershell script template to rule them all