Press "Enter" to skip to content

Category: Administration

Third Party Vendors and Missing tempdb Space

Published 2024-06-10 by Kevin Feasel

Tanayankar Chakraborty troubleshoots a strange issue:

Issue

An issue was brought to our attention recently where an azure SQL DB was throwing TempDB related errors although the customer felt that the TempDB usage never came close to the value published in the official Microsoft document. Here’s the error the customer had complained about:

Error

Here is a more detailed error text :

The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.’. Possible failure reasons: Problems with the query, ‘ResultSet’ property not set correctly, parameters not set correctly, or connection not established correctly.

This was an interesting problem and, admittedly, I didn’t predict the twist.

Comments closed

Auditing a SQL Server: Discovery and Documentation

Published 2024-06-06 by Kevin Feasel

Ben Johnston begins a new series:

Inheriting a server, whether as an inexperienced user or an experienced DBA, has many challenges. It’s very helpful to evaluate the servers, document issues, and record the current configuration. It can also be beneficial to evaluate the current state of servers you have owned since they were built or even in preparation for a formal audit. The discovery and documentation phase of an audit will set you up for later detailed audits, or it may serve as the complete scope of the audit.

This is the first part of a series on evaluating and auditing SQL Server and Azure SQL Database. Auditing SQL is a very broad topic, so I have broken it down into several sections. This section will cover the major categories that should happen in a basic SQL Server discovery audit. An initial examination of your environment is primarily documentation and looking for critical issues. This includes basic server and SQL engine configuration, physical configuration items such as disk and memory, critical items such as backup state, database configuration, basic code smells, application integration, and high-level security configuration.

Read on for some of the things Ben looks at.

Comments closed

I/O Analysis for SQL Server on Azure VMs

Published 2024-06-05 by Kevin Feasel

Ebru Ersan announces a new preview feature:

It is not easy to understand what’s going on when you run into an I/O related performance problem on an Azure Virtual Machine. It is a common, but complex problem. What you need is to figure out what’s happening at both the host level and your SQL Server instance where often, correlating host metrics with SQL Server workloads can be a challenge.

We developed a new experience that helps you do exactly that.

Click through to see how it works. Given that awful disk latency is a common problem in the cloud, this may at least tell you if you have things set up correctly.

Comments closed

GRANT Operations in Postgres

Published 2024-06-04 by Kevin Feasel

Shaun Thomas takes us through GRANT operations and roles in Postgres:

Not every database-backed application needs to be locked down like Fort Knox. Sometimes there are even roles that leverage blanket access to large swathes of available data, if not every table, simply for auditing or monitoring purposes. Normally this would require quite a bit of preparation or ongoing privilege management, but Postgres came up with a unique solution starting with version 14: predefined roles.

This topic comes up relatively frequently in Postgres chats like Discord, Slack, and IRC. Usually it’s along the lines of: “We have a low security application but have separated read and write access from the table owner to avoid accidents. That user should still be able to read or write any table in the database. What do I do?”

This is an area where Postgres and SQL Server are using the same terms but aren’t quite speaking the same language.

Comments closed

Data Encryption Options in Postgres

Published 2024-05-31 by Kevin Feasel

Greg Nokes gives us the options:

Operating system or disk-level encryption protects entire file systems or disks. This method is application-agnostic and offers encryption with minimal overhead. Think technologies like luks in Linux or FileVault in MacOS.

Read on for four options. They’re very similar to options available in SQL Server, so it’s easy enough to compare implementation ideas.

Comments closed

Finding Free Space in SQL Server Database Files

Published 2024-05-24 by Kevin Feasel

Greg Robidoux checks for free space:

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

In this tip we take a look at a few ways to find the current free space within a database, so you can better manage your database files.

Click through for several techniques.

Comments closed

Tracking Transaction Log Space in SQL Server

Published 2024-05-23 by Kevin Feasel

Greg Robidoux keeps track of available space:

Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?

Read on to see how. This is almost assuredly a part of any monitoring system but if you’re working to roll your own or find yourself in an environment without such a system, it’s a good thing to track.

Comments closed

Open Questions on Fabric Administration

Published 2024-05-23 by Kevin Feasel

Paul Andrew asks some great questions:

Microsoft Fabric is a big product with lots of different data handling capabilities. From a data engineering perspective creating and innovating with Fabric as a unified tool is a great experience, ultimately delivering data insights for the business and adding value, nice! However, as with all new developments, the creativity is the fun part. The governance and movement of code into production is less fun and can become the hard/ugly part if the change management, platform and governance aren’t mature enough.

Paul doesn’t have answers for us, though I do think many of these will eventually have answers most people find reasonable.

Comments closed

Using the pg_repack Extension

Published 2024-05-16 by Kevin Feasel

Muhammad Ali tries out an extension:

Regular updates and deletions within PostgreSQL tables can lead to various issues such as bloat, fragmentation, and a decline in performance over time. These challenges can significantly impact the efficiency and reliability of the database, potentially affecting critical operations.

To address these concerns, PostgreSQL introduced the pg_repack extension, which provides a robust solution for managing table maintenance without disrupting the production environment. By allowing tables to be rebuilt online, pg_repack tackles bloat and fragmentation issues, ensuring that database storage remains optimized and performance is consistently maintained.

Read on to see why vacuuming might not be enough and what pg_repack does.

Comments closed