Press "Enter" to skip to content

Category: Administration

Finding Necessary Permissions for DMVs

Randolph West has a script for us:

Part of my job at work is to update Transact-SQL reference content. System dynamic management views (DMVs) have permissions that are managed in the SQL Server Database Engine source code, so it’s a little tricky to figure out the required permissions for a specific DMV.

This blog post provides a stored procedure that uses sys.fn_builtin_permissions to calculate permissions, but keep in mind that any custom permissions that you’ve implemented, can override the defaults.

This can serve as a quick check for whether your user account has the necessary permissions to access DMVs in a particular database.

Leave a Comment

Configuring Alerts in Azure SQL Managed Instance

Aleksey Vitsko wants an alert:

You have an Azure SQL Managed Instance and you want to set up SQL Server alerts for errors with severity 17-25, similar what you would do for an on-prem SQL Server. You go to the SQL Server Agent folder in Object Explorer, expand it, and whoops – there is no Alerts folder.

As of time of writing this article (June 2025), Azure SQL Managed Instance doesn’t have this functionality, and we don’t have any ETA on when it will be implemented. So, how can we setup alerts in Azure SQL MI to notify us when there are issues?

Read on for a workaround and a warning.

Leave a Comment

Auditing Specific Data Access in SQL Server

Andreas Wolter wants to focus in on specific database objects:

In this article I want to share a targeted approach to audit access to specific objects within a database in Microsoft SQL Server.

  • In my last article, Evading Data Access Auditing in Microsoft SQL Server – and how to close the gaps, I showed multiple approaches to gain access to a chunk of sensitive data using the statistics object in SQL Server. The hardest one to capture is access to data that is exposed via the dynamic management function (DMF) dm_db_stats_histogram. This requires an additional Audit Specification in the master database for this system object. In the end we required 3 different Audit Action Groups to cover all the methods used to read data from our example table.

Read on to see what you can do as of SQL Server 2022.

Leave a Comment

Logging in PostgreSQL

Elizabeth Christensen saves some information:

A modern-day Postgres instance creates robust and comprehensive logs for nearly every facet of database and query behavior. While Postgres logs are the go-to place for finding and debugging critical errors, they are also a key tool in application performance monitoring.

Today let’s get set up with logging for Postgres – starting with the basics of what to log, how to log what you want, and as reward for your hard work – how to use these to monitor and improve performance. The Postgres docs on logs are excellent, so please consult those for the most up to date and comprehensive configurations. This blog reads between the lines a bit beyond the docs to offer some practical advice and settings. As always, your mileage may vary.

Click through for several tips and a lot of information on the topic of logging.

Leave a Comment

Auditing in Oracle

David Fitzjarrell digs into the options available for security audits in Oracle:

Security rears its ugly head yet again, this time with security audits. Depending upon the RDBMS auditing can be a breeze or a nightmare. Oracle has done wonders in making this task simpler for the DBA to enable, configure and maintain. Let’s take a dive into this topic and shed some light on it.

By default when Oracle is installed the newest foray into auditing, the Unified Audit Trail, is disabled. In releases prior to 12 basic auditing was automatically available, but it wrote to possibly three locations:

Read on for those three locations, how things have changed with Unified Audit Trail, and some of the information you can get back from it.

Leave a Comment

The Importance of Running DBCC CHECKDB

Kevin Hill has some advice:

You just ran DBCC CHECKDB for the first time in a while (or maybe ever) and saw something you didn’t expect: the word corruption.

Take a breath.

Don’t detach the database.
Don’t run REPAIR_ALLOW_DATA_LOSS.
Don’t reboot the server or start restoring things just yet.

There’s a lot of bad advice floating around from old blogs, well-meaning forum posts, and even some popular current LinkedIn threads. Some of it might’ve been okay 15 years ago. Some of it is dangerous.

Let’s dig in.

Click through to dig in.

Leave a Comment

Installing SQL Server Instances via dbatools

David Seis digs into another dbatools cmdlet:

As DBAs we install SQL Server for various reasons regularly. If you could save time for each installation for more critical tasks, would you?

In this blog post, we will audit the dbatools command Install-Dbainstance. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Install-Dbainstance is a powerful tool to automate the install and configuration of a new SQL Server instance. It works well in scenarios that require frequent deployments of SQL Server instances.

You can definitely automate installation of SQL Server without the cmdlet, but the dbatools team does a good job of laying out what’s possible that you might not necessarily get just from the config script that the SQL Server installer spits out (and uses when you next-next-next your way to success).

Leave a Comment

ABORT_QUERY_EXECUTION in SQL Server 2025

Joey D’Antoni pulls out the big guns:

However, SQL Server 2025 gives us a bigger hammer (DBAs love hammers). Building on top of the query store hints feature that was added in SQL Server 2022, ABORT_QUERY_EXECUTION simply blocks the exection of known problematic queries.

Read on to see how this works. Note that it will prevent the query from running at all, ever. As a result, if your main concern is “Hey, don’t run this during the busiest hours of the data for this database,” this particular hint is overkill.

Leave a Comment

Building 9’s with PostgreSQL High Availability Features

Semab Tariq explains some of our options:

When you are running mission-critical applications, like online banking, healthcare systems, or global e-commerce platforms, every second of downtime can cost millions and damage your business reputation. That’s why many customers aim for four-nines (99.99%) or five-nines (99.999%) availability for their applications

In this post, we will walk through what those nines really mean and, more importantly, which PostgreSQL cluster setup will get you there.

Read on to see what you can do to get to each 9, as well as some unexpected risks to keep in mind along the way. And, of course, each rung up move up the ladder will generally cost you more money and administrative effort.

Leave a Comment

Thoughts on Shrinking Databases

Chad Callihan lays out some thoughts:

Unlike your lawn, which benefits from a regular mowing to stay healthy and looking nice, a database isn’t meant to be regularly shrunk just to keep its size under control.

Read on for Chad’s take, which is one I agree with wholeheartedly. There are specific circumstances that merit shrinking a database. The main use case is when you have a significant level change: you’ve archived or deleted a bunch of data. There are very few other valid use cases, especially when you consider that databases typically have positive growth.

Leave a Comment