Voice of the DBA

Author: way0utwest

Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory
I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

How The Procedure Works

We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

I won’t show it, but this works in the following way:
1. create three table variables with a single ID column
2. insert data into these two tables from backupset where the date is older than the parameter passed in.
  1. backup_set_id from backupset
  2. media_set_id from backupset
3. insert data into the third table that matches the backup_set_id from the table in A
4. start a transaction
  1. delete from backupfile the matching backup_set_id values
  2. delete from backupfilegroup the matching backup_set_id values
  3. delete from restorefile the matching backup_set_id values
  4. delete from restorefilegroup the matching backup_set_id values
  5. delete from restorehistory the matching backup_set_id values
  6. delete from backupset the matching backup_set_id values
  7. delete from backupmediafamily where the media_set_id values match
  8. delete from backupmediaset where the media_set_id values match
5. commit the transaction (or rollback if errors).
This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

Know Your Tools

This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

It is not.
July 3, 2024
Xp_cmdshell Use Cases

I had someone ask me recently how to run xp_cmdshell on a Linux version of SQL Server. I told them you can’t, as it’s an unsupported feature and not one that I expect to see released. I had to double-check, since I did think that supporting a BASH shell was a possibility, but it wasn’t added to the product.

In the feedback forums, I saw a request for xp_powershell, though the feedback from MS is to use CLR for this. They suggest external access permissions, but those aren’t supported on Linux. I also didn’t see a request for shell scripts added, and I’m not sure I want one.

A few years ago, I wrote a piece on the dangers of xp_cmdshell, as this does create a security risk. I can be mitigated, but the modern world is complex and it can be easy to make a mistake here. I’ve used xp_cmdshell often without issues, but I’ve also known the risks and tried to mitigate them with controls on the machine, network, and who can execute the procedure.

I’ve seen people use xp_cmdshell for a number of tasks, like exporting a result set to a file, checking disk space, moving files after a BULK INSERT or backup, or some other task that is tightly related to actions taking in T-SQL. This can be a very handy utility for many administrators.

Today I’m curious what are your use cases. Where do you use this utility, or where is it much easier than adding a PowerShell step in an Agent job? Similarly, do you use xp_fileexist or other XPs to do things are are outside of the realm of T-SQL. Leave a comment below and let us know how you use this stored procedure. Or in which situations this has proven to be useful in the past.

And maybe vote for a BULK EXPORT 😉

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

July 3, 2024
DBAs, Organizing Your SQL Scripts in Git

I wrote an article that was published on SQL Server Central on how to get your scripts into Git. This post adds a few more thoughts on how you might get started.

This is part of my series on git that is designed to help people get started using version control in their daily work. You can see all my other posts on Git as well.

Organizing My Scripts

I showed a sample folder that looked like this, with a main area of scripts and then a subfolder for reports.

As a DBA team, I want to ensure we all easily can find things. If we don’t have a lot of scripts, I might keep most in a single folder. However, if this becomes 3 or 40 scripts, it’s easy to make mistakes or have too many similar things.

What I might organize things slightly better like this:

Here I’ve moved the Diagnostic queries from Glenn Berry into their own folder. Those I might run more rarely, though I might update them more often. Getting them into their own folder lets me move them out of the way. You could organize those by version, but the names keep them separate, so I’d probably just keep them all there.

I added a “ETL” folder for specific scripts related to that function. I might need those regularly, but this helps me find them. If I had different types of ETL stuff, like on-prem and AWS, or maybe different apps (“Sales DW” vs. “Inventory ETL”), I might put those in subfolders below there.

I also renamed the who_is_active.sql to “common_who_is_active_scenarios” where I have some calls with specific parameters set.

I didn’t do this, but looking at this, I’d probably add a “installation scripts” folder where I moved the sp_WhoIsActive.sql and other install versions of scripts into that location.

What I’m trying to do is just get DBAs to easily and quickly find scripts without accidentally picking the wrong scripts. This helps in pressure situations and also helps onboard new team members.

I didn’t show how to update and version scripts, but I’ll do that in a new SSC article.

July 1, 2024
The Cost of Rework

I often deal with customers who are looking to improve the way they build and manage database software. These could be small companies or large enterprises, with teams of developers trying to enhance their application software to solve new business problems. Often those enhancements require new data, with the related schema changes in a database. Even if there isn’t any new data, often we need to query data in new ways, combing, filtering, aggregating, and otherwise transforming data into extra information that a business can use.

Solving many of these problems is iterative by nature. We try one thing, then another. Often a developer might experiment with a data model or query, trying to match a requirement they’ve been given. Once they produce a solution, we may find problems in testing, or far too regularly, in production. These could be data-related issues, where the developer hasn’t considered values in their solution (zeros, blank or long strings, extreme dates, etc.). These could be logical errors, where the developer just made a mistake. There could also be a problem with the requirement, where the customer provided an incomplete or incorrect specification to the developer.

In any of these cases, what often happens is rework. We need to re-look at the code we’ve written and change it in some way. This isn’t a complete rewrite, but looking for the logical error, incomplete algorithm, or poorly performing structures. Then we need to (test it and ) move the code back through our process of deploying to QA, UAT, production and anywhere else the problem exists. The further we are in the process (closer to production) the more costly the effort to rework code.

Some rework is unavoidable, but I think quite a bit isn’t. If we wrote better-performing and cleaner code the first time and had a pattern for testing our code, we might avoid a fair amount of rework. If we caught silly mistakes early, we could get more done.

I think that often developers minimize the effort required to perform rework. They think small changes are easy. After all, we already know the problem space, we’ve looked at it, and the fix should be quicker than the initial analysis and development.

Or is it?

One of the things I’ve seen is that everyone is busy. We are pulled in many directions by many different people and work on lots of different problems. Even when we work on the same application every week, it takes some time to remember the context under which we wrote the code. If it’s not our code, then we spend time trying to understand how the original developer approached the problem. Without good specifications or tests, we might not view things the same way during rework. We often fix one problem but create another.

Automated testing should help here, as the tests should codify the understanding of the requirements, but too few database developers use automated testing for their code. Even developers who embrace testing from the application might not have sufficient test data to ensure their code will work in production.

Most of us are expected to do more work this year than last year. We’re constantly battling a lack of resources. We can’t necessarily write more code, but we can learn to be more efficient. We can learn to reduce our rework by writing code better the first time. That takes some effort and knowledge. It takes working as a team, sharing successes and struggles, and adapting our code to use patterns and avoid anti-patterns. It requires some automation to embrace static code analysis and testing to avoid silly mistakes getting deployed and wasting time. More importantly, it takes support from management to spend time learning as a team to produce better code and avoid silly mistakes.

A little investment in learning and practice goes a long way. Unfortunately, too many people, both staff and management, are unwilling to commit. Except in fixing bugs and spending time on rework. Far too many people see that as an regular, accepted part of software development.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

July 1, 2024