Press "Enter" to skip to content

Category: Administration

Database Snapshots in High-Availability Setups

Published 2025-05-14 by Kevin Feasel

Stephen Planck adds one more layer of complexity:

SQL Server’s database-snapshot feature is a wonderfully simple tool: at the instant you create the snapshot, every page in the database is marked “copy-on-write.” Nothing is copied across the wire, no blocking locks appear, and the snapshot opens immediately as a read-only database on the local replica. Queries against the snapshot see the world exactly as it looked at that moment while the live workload keeps changing pages in the primary data files. Because snapshots live only in sparse files on the server that owns them, they are not a replacement for backups—but they are perfect for ad-hoc reporting, quick “before-and-after” comparisons, or a safety net when you want an easy way to back out a risky change that should finish within minutes or hours.

But read on to see how they interact with high-availability features such as transactional replication and availability groups.

Comments closed

Data Recovery in SQL Server without a Backup

Published 2025-05-13 by Kevin Feasel

Rodrigo Riberio Gomes digs in:

In more than 10 years of experience, I have dealt with cases where someone has performed incorrect operations on a table, such as updating or deleting wrong rows, in a SQL Server database that does not have full backups available. There are multiple reasons for no full backup: corrupted backups, taking too much time to restore, etc.

In this post, I want to show an alternative for these cases, an ace up one’s sleeve, that you can use to recover data. This method also provides a deep understanding of the internal workings of how your SQL Server stores data. So, in addition to learning how to recover data, you will gain more insights into the internals of SQL.

Read on to see how. Rodrigo also points out some limitations or things that would need to change if you have index compression. I consider this a very neat thing you might need to know but never want to use.

Comments closed

Restoring Multiple Differential Backup Files

Published 2025-05-08 by Kevin Feasel

Tim Radney violates Betteridge’s Law of Headlines:

I was recently asked if you can restore multiple differential backups in preparation for a migration. I responded that yes, technically you can restore multiple differential backups, however it will not speed up your cutover.

As soon as I read the first sentence, the answer in my head was “Yes, but why?” Tim explains the person’s reasoning and then demonstrates that this reasoning doesn’t quite work.

Comments closed

Review those Logs

Published 2025-05-08 by Kevin Feasel

Kevin Hill has a public service announcement:

Most SQL Server crashes don’t come out of nowhere.
They leave breadcrumbs – red flags that something’s not right. The problem? If you don’t know where to look, you miss the signs…until it’s 2am and your CEO’s calling.

Let’s talk about how to listen for those whispers before they turn into full-blown alarms.

Click through for some advice on the topic. I’ll also note that everything Kevin mentions, you can automatically retrieve and centralize in a monitoring system, and once you have more than a couple of SQL Server instances, I’d recommend doing so.

Comments closed

Tracking Wait Classes in Oracle

Published 2025-05-07 by Kevin Feasel

Kellyn Gorman looks for performance bugbears:

When we talk about optimization in Oracle, many DBAs immediately jump into metrics like CPU utilization, I/O throughput, or specific SQL queries. But there’s a critical layer of understanding that often gets overlooked and that’s Oracle Wait Classes. These categories are essential to making sense of what’s really going on inside your database, and they’re often the starting point for diagnosing and optimizing performance.

Read on to learn what wait classes are and why they’re so important for Oracle DBAs.

Comments closed

Checking Index Utilization in Oracle

Published 2025-05-06 by Kevin Feasel

David Fitzjarrell wants to see which indexes are in use:

Database performance is one of the biggest areas for DBAs to address, measured primarily in time. Users usually complain when queries “take too long” and immediately run to the DBA team to register their disappointment. One avenue used to address such concerns is the creation and maintenance of indexes, and as any DBA with any experience can attest, simply throwing indexes at a table isn’t necessarily the smartest play; a major concern is if the index is actually being utilized. Oracle, in its wisdom, has provided tools to monitor these objects in the form of system views. Let’s look at what is available and how to get the most benefit out of them.

Click through for some scripts and information on how to tell whether an index is in use or not.

Comments closed

Purging Data from Large Tables

Published 2025-05-02 by Kevin Feasel

Matt Gantz deletes the elephant:

Purging data from a table is a common database maintenance task to prevent it from growing too large or to stay in compliance with data retention. When dealing with small amounts of data, this can be accomplished by a simple delete with no issues; however, with larger tables, this task can be problematic. Deleting records requires a lock that can block other processes from writing or even reading the data (depending on your isolation level). In this article I will share a technique I have used to work with some very large tables.

I’ve followed exactly this pattern many a time, and it works quite well if you have an appropriate supporting index.

Comments closed

Vacuum Queries in PostgreSQL

Published 2025-05-01 by Kevin Feasel

Dave Stokes shares some queries:

 I am (slowly) adding handy PostgreSQL queries to my GitHub, and Vacuum is the newest category.  The end goal is to have a compilation of queries for those of us who need to keep an instance healthy.

I’ve always liked these compendia of helpful queries. The downside is that these are PDF images, making them harder to use. But even so, Dave is accepting pull requests.

Comments closed

Oracle Memory Management Tips

Published 2025-05-01 by Kevin Feasel

Kellyn Gorman shares a few tips with us:

After the initial overview of memory monitoring in Oracle, particularly around the SGA and PGA, I wanted to take a deeper dive into the most common Oracle memory configurations. These include how memory settings are defined, how they interact, and the combinations you’re likely to encounter across different workloads.  Oracle memory is a no-brainer for many of us who’ve been in the space for an extensive time, but I’ve come to realize, it can be a bit overwhelming and especially with recent changes from Oracle 12c on.

Click through for Kellyn’s guidance.

Comments closed