Press "Enter" to skip to content

Category: Administration

Resuming Data Movement for an Availability Group

Published 2024-08-21 by Kevin Feasel

Chad Callihan gets things moving after a few 1s without enough 0s clog up the pipe:

Keeping an Always On Availability Group healthy is crucial, and seeing a non-synchronizing database in an Always On High Availability Group can give you a sinking feeling (pardon the pun). Disregarding the reason for the syncing issue, there are a few ways to resume syncing and get your setup back in the green.

Let’s look at resuming using the SSMS GUI and running a SQL statement.

Read on for the process. I appreciate that Chad also includes the T-SQL operation to do this.

Comments closed

Error 1119 on Database Shrink

Published 2024-08-21 by Kevin Feasel

Kendra Little troubleshoots an error:

At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:

Msg 1119, Level 16, State 1, Line 11 Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using the object that this IAM page belongs to. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error.

Click through to see how Kendra was able to get around this issue.

Comments closed

Auditing a SQL Server Database Configuration

Published 2024-08-20 by Kevin Feasel

Ben Johnston continues a series on auditing:

This continues my series on auditing SQL Server. The fist parts covered discovery and documentation, server level hardware audits and SQL Server engine level audits. This section examines database configuration audits. As with the previous audit sections, the boundaries for the audit can be blurry. I try to stick to configuration items only, but I also discuss some code smells and items that can impact performance or might be covered in a code review. You will need to determine the scope of your audits and how much you want to cover in this portion of the audit.

This follows the patterns of the previous audits, starting with a list of items to validate, followed by key points to examine for each of those items, and ends with scripts or tactics to gather the actual audit results. As with previous audits, there are multiple methods to examine each item, but I generally prefer scripts due to their repeatability, especially when they need to be run by another team.

Read on for a general template, followed by details on each section.

Comments closed

SQL Server on Linux for Disaster Recovery

Published 2024-08-19 by Kevin Feasel

Mika Sutinen shares a use case:

SQL Server on Linux has been an option for quite a while already, but I have to admit that personally, I have previously not been a very excited about that idea. This is mostly due to some complexities and limitations of SQL Server on Linux. However, the recent CrowdStrike incident made it quite obvious, that there’s a great use case for it. And that use case is Disaster Recovery.

The idea of having some operating system variety makes sense, though read the whole thing, as some companies will use components that SQL Server doesn’t offer for Linux.

And if you want a deep dive into using SQL Server on Linux, I’ll do a bit of self-promotion and reference my video series on the topic.

Comments closed

Reclaiming Space after a DELETE Operation

Published 2024-08-19 by Kevin Feasel

Andy Yun checks disk usage:

In my current role at Pure Storage, I have the privilege of working with two amazingly smart, awesome SQL Server nerds; Andrew Pruski (b) and Anthony Nocentino (b). We often find ourselves facing interesting questions about SQL Server and storage, and today was no exception.

Andrew had a customer who wanted to know what happens on our FlashArray, from a space usage perspective, when they first delete a large volume of data in a database’s data file, then subsequently shrink the database’s data file.

Read on for that answer. This answer also applies to other storage solutions as well.

Comments closed

Creating a Role for Procedure Execution

Published 2024-08-16 by Kevin Feasel

Andy Brownsword brings out the hangman:

We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.

Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.

So let’s fix that.

The nice thing about this role is that you can then introduce module signing and allow the stored procedures to do things that you might not want regular users to do, such as truncate tables, start and stop SQL Agent jobs, etc. That way, users don’t have these advanced permissions, but the application (whose account has the stored procedure execution role) can do what it needs to do. You can, of course, also have other roles, like one for the kinds of things I mentioned and another to execute most stored procedures. Maintaining that gets a little trickier, but is doable.

Comments closed

Blob Storage Account Lifecycle Maintenance

Published 2024-08-09 by Kevin Feasel

Andy Brownsword deletes some files but wants to keep other files:

A hierarchy of directories which contain files. That’s how we typically think about file storage. That’s not quite the same everywhere. In Blob Storage a file can appear to be in a directory, but when it’s removed so is the directory.

This can occur when using Lifecycle Management to help purge legacy blobs, which can be unexpected. Let’s look at a way we can help remediate this.

One important thing to remember about Azure blob storage accounts and S3 buckets is that there’s really no concept of a directory structure. It’s all keys, where your key might be dir1/dir2/dir3/file.txt. This is a bit different for Azure Data Lake Storage Gen2 and its notion of hierarchical namespaces (i.e., folders). But Andy does walk through some of the consequences of this and how to work with lifecycle maintenance policies to delete only certain sets of files.

Comments closed

Filesystem Access for Database Restoration via dbatools

Published 2024-08-07 by Kevin Feasel

Andy Levy shares a lesson learned:

While performing an instance migration this spring, I happened upon something I didn’t expect in [dbatools](https://dbatools.io/). It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (via Restore-DbaDatabase) wouldn’t execute.

I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the restore database statements directly from within Management Studio, the databases restored without issue.

After doing quite a bit of digging, I managed to find the reason.

Read on to learn more about necessary permissions, as well as the issue Andy hit, as well as the solution.

Comments closed