A Fine Slice Of SQL Server
So I’ve had to deal with this with some clients recently, and the problem with CDC, of course, is that if you change, add, drop columns from CDC tables, or tables that are covered by CDC, rather, the current change capture table does not reflect those changes. You have to do some work to figure it out. What I’ve got in this video is I’m just going to, a script that I can walk through, I can hit F5 on it.
Click through for the script, information on capture instances, and more.
Leave a CommentI have a SQL Server named instance that is used by various resources. There may even be reports and other artifacts that access the named instance which we don’t know about. Having a named instance means in a recovery situation we must have a SQL Server installed as a named instance with the same name. This impairs our recoverability as well as our ability to upgrade SQL Server versions because we must retain that named instance. Is there a path to migrate to a default instance without potentially breaking things?
Click through to see how.
Leave a CommentI deployed a schema change to 30 servers using my deploy-at-low-priority script via SSMS multi-server query. Some of these servers were small with almost no activity, so I expected them to finish within seconds. I opened another multi-server connection to check on progress and none of the small servers showed any sign of having completed. Then all 30 finished at the same time.
Read on to see what happened and why.
Leave a CommentChristopher Winslett takes a look back:
Over the years we have written a lot about how data gets into Postgres, how it sits on disk, and how indexes help you find it again. Some of that advice was written against Postgres 10 or 11. A surprising amount of it is still exactly what we would tell you for the upcoming Postgres 19 release. Functionality described here is based on current betas; minor details may still change before GA.
This post revisits Crunchy posts in the “load, storage, indexes, and partitioning” bucket: what we wrote, which version moved the needle, and what we would tell you to do now. Along the way: async I/O, more resilient
COPY, LZ4 by default, richer BRIN shapes, skip scan, and smoother partition operations.
I like this sort of article because it helps old-timers in an industry recalibrate their expectations based on the current state of technology.
Leave a CommentJeff Iannucci announces a new stored procedure:
This tool will give you a fast, comprehensive picture of a SQL Server instance. It gathers the kind of information you would otherwise collect by clicking through a dozen dialogs and running a handful of scripts, and it flags potential issues so you can decide what to deal with first. The findings are organized into categories like Recoverability, Security, Availability, Integrity, Reliability, and Performance, and each one comes with details and an action step so you aren’t left guessing about what to do next.
Click through for the script and how you can use it.
Leave a CommentJeremy Schneider provides an alternative:
Kubernetes metric
container_memory_working_set_bytesis used for evicting/killing pods with too much memory use, especially ifmemory request < limit(don’t do this with Postgres). The metric is calculated from cgroups v2memory.statascurrent-inactive_file[source].You’d assume it’s a good metric for memory usage in kubernetes. But with Postgres, this metric is very inaccurate for memory utilization and doesn’t tell you at all if you’re going to OOM crash your database.
Click through to understand what goes wrong, why it does, and what you can use instead.
Leave a CommentVlad Drumea performs some tests:
After the ANCPI hack that took down Romania’s land registry, Andrei Avădănei, CEO of Bit Sentinel and founder of DefCamp, published on LinkedIn a detailed proposal for a national offensive security program.
It covered pentesting frameworks, vulnerability disclosure, continuous monitoring, and accountability measures. The proposal was thorough, logical, and exclusively focused on prevention and detection.I left a comment suggesting one addition: mandatory disaster recovery simulations.
Can institution X recover after their entire production environment is encrypted? If so, how long does it take and what data is lost? Are there backups? And if yes, are they actually viable, or are they Schrödinger’s backups, where you only find out whether they work at the exact moment you need them?This exchange made me realize that organizations, especially in the public sector, rarely consider doing disaster recovery tests.
I’ve been on the edges of DR scenarios at prior jobs, including one at a state agency. Most of the time, the tests have to be hypothetical or piecemeal because we rarely had the hardware to support a full switch-over, or the budget to spin up an equivalent set of hardware in a different region.
Leave a CommentIn Part 6 of this special Postgres in Production deep dive series, Ryan Booz asks a question that determines how useful
pg_stat_statementscan be for you at all: do you have a high cardinality workload? This episode covers what that actually means, why ORMs, dynamic SQL, and AI-assisted development tools generate more unique queries than you might expect, a side by side demo of the same workload on Postgres 17 and Postgres 18, and the concrete checks that tell you whetherpg_stat_statementsis losing the data you need for query tuning.
Click through for the video and transcript.
Leave a CommentMartyn Jones hunts down the ghosts:
The concepts discussed in the previous blog post can also be seen in the transaction log. The logical removal of rows (by marking them as ghost records), the associated allocation metadata updates, and the eventual physical removal of those rows by the ghost cleanup process are recorded as individual transaction log operations.
The demo code uses the undocumented function fn_dblog(), this provides a clear view of the internal sequence of events that SQL Server performs during a delete operation so the individual physical changes required to implement ghosting, and later, cleanup can be studied.
Click though to see how SQL Server marks ghost records, tracks where they are, and performs cleanup.
Leave a CommentFabiano Amorim takes us through a thought process:
SQL Server DBAs routinely rely on automation: SQL Server Agent jobs, maintenance plans, refresh processes, restore scripts, replication cleanup, CDC operations, cross-database modules, and temporary objects. These workflows are often trusted because they’re normal – and that’s precisely why they deserve more attention.
The goal of this article is not to provide exploit recipes. Instead, it’s to help DBAs answer a practical question: how do I know whether my SQL Server instances are exposed to this kind of risk, what should I monitor, and what should I change when I find a problem?
Click through for the process.
Leave a Comment