script

Drop All Redundant Indexes In Every Database All At Once

2023-02-262023-02-26
SQL Server Database Development, SQL Server Database Engine, Tools and Scripts

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.

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

2022-11-022023-05-31
SQL Server Database Engine, Tools and Scripts
3 Comments

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.

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?

The Best Solution for SQL Server Partitioning Sliding Window

2022-05-302022-12-11
T-SQL, Tools and Scripts

Turns out there’s a shortage of solutions online for easily managing partitioned tables in SQL Server. Well… I guess I’ll have to do it myself!

Detect Weak Passwords in SQL Server

2022-02-012022-12-25
T-SQL, Tools and Scripts
2 Comments

This is a T-SQL script that I’m cross-publishing with the official Madeira Data Solutions blog.

This script generates various permutations and variations of common and weak passwords and uses the PWDCOMPARE function to check whether there are any enabled SQL logins that have these passwords.

Detect Low PAGE Compression Success Rates in all Databases

2022-01-17
SQL Server Performance, Tools and Scripts
1 Comment

Thanks to Paul Randal and his recently published blog post: “The Curious Case of… tracking page compression success rates“, I finally discovered what was the purpose of the column page_compression_success_count from the system function sys.dm_db_index_operational_stats, and how to use it.

Finding a use for Extended Properties in SQL Server

2021-07-27
SQL Server Database Development, T-SQL, Tools and Scripts
2 Comments

“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.

One handy Powershell script template to rule them all

2021-06-082022-12-11
Tools and Scripts
1 Comment

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).

Using Extended Events as a Buffer

Assume you have some kind of process outside the SQL Server which is supposed to record some kind of database activity. There are many ways to do it, most of which require constant and/or stable connection to the database. But what if that’s not good enough? Let’s consider our options.

Control SQL Jobs based on HADR Role – Taking it to the Next Level

2020-05-26
SQL Server Database Engine, T-SQL, Tools and Scripts
3 Comments

In one of my previous blog posts, Automatically Enable or Disable Jobs based on HADR Role, I provided a solution for automatically enabling or disabling scheduled jobs in combination with Availability Groups and Database Mirroring. In this blog post, I’m going to build upon my previous solution and improve it even further – taking it to the next level of automation!