T-SQL Tuesday #158 – When Worst Practices are the Best

This month’s T-SQL Tuesday is an especially interesting one! Hosted by Raul Gonzalez (b|t), Raul asks us to talk about “Worst Practices” and when could they be a good thing to use. After all, the actual “best practice” is – well… it depends!

I, personally, have a couple of “Best-Worst Practices” that I tend to encounter quite a lot, and have already talked about in some of my blog posts in the past.

So, let’s talk about one of them:

Never Shrink Your Database – Unless…

Shrinking databases and database files in SQL Server is a widely known “worst practice”.

Usually, it’s because it’s assumed that the database files are expected to auto-grow again after the shrink.
So, in truth, it’s not the shrink itself that’s the problem… It’s the auto-growth!

But… What if you DON’T expect the database file to auto-grow back to what it was before?
For example, what if you truncated/deleted/migrated/archived huge chunks of data from your database, which you don’t expect to be returned later? Or what if you performed a massive data compression or migrated to clustered columnstore indexes, which reduced your data size significantly?

Then sure, why not shrink your data file in such cases?
Perhaps you could use that free disk space for other things, or even for reducing hardware expenses.

However, it’s important to remember that there are cases where a shrink can take a very long time. Check out my blog post about troubleshooting long-running shrink operations for more details and ways to remediate it.

With that being said, auto-growth of DATA files is not too bad if you have Instant File Initialization (IFI) enabled, and you don’t have Transparent Data Encryption (TDE) enabled. Otherwise, SQL Server will have to “zero out” the newly allocated file space before using it (which is what would be causing all the performance problems).

That’s about DATA files. But what about TRANSACTION LOG files?

Transaction Log files are a bigger problem due to a couple of reasons:

1. When growing transaction log files, they ALWAY need to be “zeroed out”, regardless of IFI/TDE. This means bad performance impact when, in the middle of a SQL command, SQL Server needs to “pause” for a moment to auto-grow the transaction log.
2. Transaction log files cannot easily be shrunk, as their contents cannot be “reorganized” (unlike data files). You can only shrink a transaction log file from its tail end (the equivalent of using the TRUNCATEONLY option).

These limitations mean that you have to be extra careful when shrinking transaction log files.

But… What if you MUST?

In several cases, I encountered a situation where due to some “abnormal” database activity, one or more transaction logs suddenly grew beyond normal proportions, and either filled up the disk to the brim or was very close to doing so.

In such cases, there’s no time to think about the performance problems of auto-growth, because your database is, essentially, in mortal danger! If there’s no room for a transaction log to grow – then the database will simply not work.

For this purpose, I have a very useful script that detects all “shrinkable” transaction log files and provides useful details about them – including the total size of the log, the active size of the log, how much it can be shrunk, when was its last LOG backup, and also a SHRINKFILE command that you can copy and paste for immediate use.

This script is available in our Madeira Toolbox on GitHub:

• Shrink All Shrinkable Transaction Logs.sql

The same script is also useful for reducing high VLF counts, but that’s an unrelated topic.

Other Best Worst Practices

If you’re interested, you can check out some other posts on my blog having to do with useful worst practices:

• Finding a use for Extended Properties in SQL Server
• Fun with DATETIME Arithmetics and Even more fun with DATETIME arithmetics!
• I learned to love WHILE (true) and you should too
• The Asynchronous Ledger Trick for Fast SQL Server Insert, Update and Delete Processes (good use case for a heap table)
• Using Reporting Services as your Software Frontend