Press "Enter" to skip to content

Category: Administration

The Basics of Transaction Logging

Published 2026-02-04 by Kevin Feasel

Paul Randal republishes an older post that is no longer available:

All through my career as a data professional, both inside Microsoft and as a consultant, I’ve found that one of the most misunderstood parts of SQL Server is the transaction log. Lack of knowledge of how the transaction log works and needs to be managed, or just simple misconceptions, can lead to all kinds of production problems, including:

  • The transaction log growing out-of-control and potentially running out of space
  • Performance issues from repeated shrinking of the transaction log
  • Performance issues from a problem known as VLF fragmentation, which I discussed in this post
  • The inability to recover to a desired point in time using transaction log backups
  • The inability to perform a tail-log backup during disaster recovery (see here for an explanation of tail-log backups)
  • Various issues around failovers and restore performance

With this post I’m starting an occasional series (now here on my SQLskills blog) on the transaction log and how it works and should be managed, and I’ll touch on all the problems above over its course. In this post I’ll explain what logging is and why it’s required.

Read on for that explanation.

Leave a Comment

Changing SQL Server on Linux Editions

Published 2026-02-04 by Kevin Feasel

Vlad Drumea swaps the edition:

In this post I cover the steps required to change (downgrade or upgrade) the edition of a SQL Server instance running on Linux.

In my previous post I’ve went through the steps of installing SQL Server 2025 on Ubuntu 24.04 LTS.
While the process is pretty straight-forward, there might be cases where someone can accidentally specify the wrong edition and only notice afterwards.

Luckily, the edition can be changed with just a few commands.

It’d be neat if it worked the same way for Windows.

Leave a Comment

Building an Ubuntu VM from Powershell Script

Published 2026-02-02 by Kevin Feasel

Vlad Drumea has a script:

I needed a fresh Ubuntu VM in VirtualBox this weekend so I figured I can take this opportunity to refresh my PowerShell based process.

Prerequisites

For this VM I’m using the following:

  • Oracle VirtualBox 7.2.6 r172322 running on a Windows host.
  • The installation media (ISO file) for Ubuntu 24.04.3 LTS.
  • 51GB of available space on the drive where the VM will live.
    Note that the default Ubuntu install is ~10GB, but I want the extra space to be able to install other stuff later on.

Click through for the script and explanation of the process. It’s not entirely automated, but Vlad does get to the Ubuntu installation point via Powershell and then takes it up from there.

Leave a Comment

Local Admin and SQL Server

Published 2026-02-02 by Kevin Feasel

Rebecca Lewis follows Betteridge’s Law of Headlines:

“Does a DBA need local administrator membership to manage SQL Server?”

The answer is simple: Local admin group membership is not required. In fact, best practices dictate that Database Administrators (DBAs) and SQL service accounts should not have local administrator rights on the host server.  This change was introduced as far back as SQL Server 2008 with a ‘secure by design, secure by default, and secure in deployment’ strategy.

Read on for the caveat of what happens when you don’t provide local admin access to your DBAs.

Leave a Comment

SQL Server and January 2026 Patch Tuesday

Published 2026-01-29 by Kevin Feasel

Rebecca Lewis takes a look at a recent security vulnerability:

Microsoft’s January 2026 Patch Tuesday included a security fix for SQL Server: CVE-2026-20803, an elevation of privilege vulnerability with CVSS score 7.2 (Important).

The vulnerability is classified as CWE-306: Missing Authentication for Critical Function. An attacker who already has high-level privileges on the SQL Server instance could exploit this flaw to escalate further — gaining debugging privileges, dumping system memory, and potentially extracting sensitive data or credentials.

Read on for more information about the CVE, what it requires to exploit, and how you can patch SQL Server to prevent it from being an issue. It’s interesting that this only affects SQL Server 2022 and 2025.

Leave a Comment

Deciding Whether to Use Clustering or Availability Groups

Published 2026-01-29 by Kevin Feasel

Brent Ozar has a take:

Sandra Delany (LinkedIn) wrote a well-thought-out blog post called, “Should a SQL Server DBA Know Windows Clustering?” She’s got about 20 years of DBA experience, and she works for Straight Path (a firm I respect) as a consultant. You can probably guess based on her background that yes, she believes you should know how to set up, configure, and troubleshoot Windows clustering. It’s a good post, and you should read it.

But… I don’t agree.

Read on for Brent’s opinion. I do agree that there are companies that don’t need high availability, and that there are plenty of problems you can run into if you don’t understand how it all works. I also think that, if you’re a DBA responsible for maintaining servers, and your company isn’t using any kind of high availability option, it’s important at least to learn how the mechanisms work so that, if HA does become important, you aren’t pushing back because of ignorance in the product.

But on the flip side, where does that rank in importance? It’s probably closer to the middle (or lower) than the top of the list. And I definitely agree that disaster recovery is generally the more important of the two if you can only have one. But even there, we have exceptions in things like stock trading systems or data warehouses, where you can rebuild the system from external sources.

Leave a Comment

Tracking Unused Indexes in PostgreSQL

Published 2026-01-27 by Kevin Feasel

Semab Tariq wants to see which indexes are in use:

Indexes exist to speed up data access. They allow PostgreSQL to avoid full table scans, significantly reducing query execution time for read-heavy workloads.

From real production experience, we have observed that well-designed, targeted indexes can improve query performance by 5× or more, especially on large transactional tables.

However, indexes are not free.

The reasons for why are very similar to what we have in SQL Server. The way to track utilization is a bit different, however.

Leave a Comment

Removing a Supersized tempdb File

Published 2026-01-26 by Kevin Feasel

Jeff Iannucci wants to smooth things out:

While reviewing the configuration of a client’s SQL Server instance recently, I noticed that they had a very curious tempdb configuration. Although it had multiple files for the eight CPU cores as Microsoft recommends for tempdb configuration, it also had another MUCH larger file with a MUCH larger growth rate.

And it turns out that when this happens, there are some knock-on effects that make it a challenge to remove such a file later. But read on for the solution.

Leave a Comment

ALTER TABLE and Partitioned Tables in PostgreSQL

Published 2026-01-22 by Kevin Feasel

Chao Li classifies a series of commands:

Does an operation propagate to partitions? Does it affect future partitions? Does ONLY do what it claims? Why do some commands work on parents but not on partitions—or vice versa?

Today, PostgreSQL documentation describes individual ALTER TABLE sub-commands well, but it rarely explains their interaction with partitioned tables as a whole. As a result, users often discover the real behavior only through trial and error.

This post summarizes a systematic investigation of ALTER TABLE behavior on partitioned tables, turning scattered rules into a consistent classification model.

Click through for 15 buckets of commands relating to ALTER TABLE in PostgreSQL and see how they handle dealing with partitioned tables.

Leave a Comment