Administration – Page 2

Ghost Records in SQL Server

Published 2026-07-06 by Kevin Feasel

When a delete operation is executed, or when an update causes a page split, a marker is set in the page header, and each row is marked to show that the record is to be deleted; this is the process of creating ghost records. The concept is to improve performance as the data is not immediately removed, and, with row-level locking, it helps minimise blocking and supports efficient concurrency under typical isolation levels such as Read Committed and above. If a transaction is later rolled back, the cost of effectively restoring the row is considerably reduced as the data doesn’t need to be reinserted, only the marker removed. Later demos will show this in action.

This is the first part of a longer series on ghost records and ghost cleanup.

Leave a Comment

Building a Lakehouse Table Health Pipeline

Published 2026-07-02 by Kevin Feasel

Gilbert Quevauvilliers builds a pipeline:

In this post I will show how I used GitHub Copilot CLI / Agent mode in VS Code to create a Microsoft Fabric pipeline that checks Lakehouse table health and only optimizes tables that require maintenance. I’ll also show the prompts I used, the issues I ran into, and how Copilot helped me resolve them.

Recently Microsoft announced Lakehouse table health. The post showed how you can check can check the health of your Lakehouse table in a very simple way.

Read on to see what this feature can do, as well as how Gilbert was able to gin up (with the help of GitHub Copilot) a pipeline to optimize lakehouse tables. What I appreciate is that Gilbert also included the errors in the process, rather than making it seem like everything with perfectly the first time around.

Leave a Comment

SSMS Tools in VS Code

Published 2026-07-02 by Kevin Feasel

Denny Cherry has a new VS Code extension:

Are you a user of VS Code, but you wish that the MSSQL extension had more of the SQL Server Management Studio features? Good news, there’s now a VS Code Plugin that gives you those capabilities in VS Code by using the “SSMS Tools for SQL Server” extension.

Click through to see what it includes. There’s a pretty decent amount of existing SSMS functionality in this extension, especially on the database administrator side, where Azure Data Studio and VSCode historically have not focused.

Leave a Comment

Too Many Tables and PostgreSQL

Published 2026-07-02 by Kevin Feasel

Laurenz Albe uncovers an oddity:

Recently, I helped a customer investigate database problems. It turned out that these problems could be traced back to too many tables in the database. Since this may come as a surprise to many users, I thought it worth the while to write about it.

Click through to see what was happening, as well as a demonstration of the problem. Granted, when you start talking about tens of thousands of tables, something has gone catastrophically wrong in your database architecture.

Leave a Comment

Master Database Compatibility Level

Published 2026-06-30 by Kevin Feasel

Jeff Iannucci explains an issue:

We were attempting to install a troubleshooting stored procedure in the master database of a SQL Server 2016 instance when we received the following error.

Msg 195, Level 15, State 10, Procedure sp_ShootTheTrouble, Line 227 [Batch Start Line 7]

‘TRY_CONVERT’ is not a recognized built-in function name.

This was unexpected, as TRY_CONVERT has been a command since SQL Server 2012. As a consequence, we were unable to install the stored procedure.

Tracking system database compatibility levels is a minor chore but an important one after an upgrade.

Leave a Comment

Downtime Woes and Microsoft Fabric

Published 2026-06-29 by Kevin Feasel

Joey D’Antoni describes a problem:

All that being said, customers buy online services and expect them to be available. One of the reasons a company chooses Fabric, Databricks, or Snowflake is the notion that those platforms for Spark and various data warehousing options will be secured, patched, and better maintained than a non-technology company could do by simply deploying Spark into Kubernetes or VMware. With that, the cloud providers have an obligation to deliver services to their customers, and deliver availability and performance congruent with their pricing.

One of the things I expect from a cloud provider is honest post-mortems when they have an outage, and maintaining a history of their outages. These histories help architects better design systems, as we can better identify weaknesses in various cloud services that we might want to design around. Azure and AWS both do an excellent job of providing detailed information around “what happened” in incidents.

But as Joey mentions, Microsoft Fabric doesn’t have the same information. It also doesn’t have a dedicated SLA. But it does have a series of outages over the past month.

Comments closed

To Shrink, First You Must Grow

Published 2026-06-29 by Kevin Feasel

Eitan Blumin gives us a riddle:

Ever ran DBCC SHRINKFILE, watched it finish without a single error… and then discovered that your file is exactly the same size as before? You run it again. Same thing. And again. Nothing. Meanwhile you know there are gigabytes of empty space in there, taunting you. Welcome to one of the most maddening little corners of SQL Server.

Read on to understand why this happens and what you can do to fix it.

Comments closed

Handling Growth in sysjobhistory

Published 2026-06-26 by Kevin Feasel

Aaron Bertrand has a lot of jobs:

In the first few days of my new role at Infios, we came across an interesting case of memory exhaustion. A whole slew of memory-related error messages would populate the errorlog, then some stack and memory dumps would appear, and then the SQL Server service would just shut itself down without warning. Some of the errors we observed (apologies, it’s a long list, but I want to make sure that any subset might land you here):

Click through to see if you have any of those issues and one possibility of what the cause might be, as well as how to deal with it.

Comments closed

Alerting on Checkpoint Time in Postgres

Published 2026-06-26 by Kevin Feasel

Jeremy Schneider shares some advice:

Checkpoint is the heart of your database. It’s buried deep inside. It’s not something everyone talks about, like well-tuned autovacuum or fast queries. But if checkpointer stops beating, then you’re dead.

In addition to its well-understood job of getting dirty pages written from cache to disk in the background, it also has many smaller jobs that are less widely known. Management of a few shared-memory config settings like sync_standby_names and full_page_writes. Fsync Batching. Deferred file unlinks. Enforcement of archive_timeout.

Click through to see what happens when checkpoint time starts increasing and one important thing you should not do.

Comments closed

The Importance of Testing Received Wisdom

Published 2026-06-23 by Kevin Feasel

Mark Wilkinson lays out an argument:

Life is full of “absolutes”. For example, the Star Trek: The Next Generation episode “The Measure of a Man” is often cited as the best episode of the series, and many folks will tell you that you should never adjust max worker threads. But once you take the time to dig in, you realize that “Darmok” is in FACT the best episode of ST:TNG, and you’ll also find a small cohort of folks adjusting max worker threads on all of their SQL Server instances. Are these people just abnoxious contrarians? No. They just did their own testing to validate the common wisdom.

Click through for an example from Mark around 64K allocation unit sizes for NTFS volumes. And I’ll give one on max worker threads. I had a consulting client at one point which had per-customer databases. Each customer was, in general, quite small, so they had thousands of databases on the instance. They also wanted high availability on the system, so they wanted each database mirrored to a different server.

If they didn’t spike max worker threads to extreme levels, the server would have fallen over simply from the weight of all of the open database mirroring connections. The actual server workload was fine and it could handle all of the open worker threads because the large majority were doing nothing. But if a zealous problem-solver popped in, ran a diagnostic, saw that they were violating “best practices,” and “fixed” the problem, that would have been a bad day.

Unrelated but similar story: the one time they did need to fail over due to an emergency, it was also a bad day. Because even if the instances can handle 2500+ databases, it turns out that having them all fail over at the same time on low-powered Azure hardware was not a pleasant experience.

Comments closed

Category: Administration