A New Word: Symptomania

Posted on July 5, 2024 by way0utwest

symptomania – n. the fantasy that there’s some elaborate diagnosis out there that neatly captures the kind of person you are, tying together your many flaws and contradictions into a single theme – which wouldn’t necessarily sort out the mess inside your head but would at least let you mark it with a little sign so people know to walk around it.

Another great definition. Maybe we should have signs we can hold up to let people know we’re feeling a little off and they should just walk around us and ignore us Winking smile

I don’t feel symptomania, mostly because I am accepting of flaws, and I don’t try to simplify the world so much anymore. I don’t see black and white but rather lots of gray. I don’t try to reduce things to a simple measure, but accept a complex way of the world as the way things are.

From the Dictionary of Obscure Sorrows

Posted in Blog | Tagged , | Comments Off on A New Word: Symptomania

Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory

Posted on July 3, 2024 by way0utwest

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).

2024-06-23 11_09_00-Zoomit Zoom Window

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.

2024-06-23 11_02_10-SQLQuery1.sql - ARISTOTLE.msdb (ARISTOTLE_Steve (82)) - Microsoft SQL Server Man

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.

Posted in Blog | Tagged , , , | 1 Comment

Xp_cmdshell Use Cases

Posted on July 3, 2024 by way0utwest

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.

Posted in Editorial | Tagged , | Comments Off on Xp_cmdshell Use Cases

DBAs, Organizing Your SQL Scripts in Git

Posted on July 1, 2024 by way0utwest

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.

2024-06-20 16_14_40-dbascripts

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:

2024-06-20 17_02_03-dbascripts

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.

Posted in Blog | Tagged , , | Comments Off on DBAs, Organizing Your SQL Scripts in Git