Voice of the DBA

Category: Blog

Deleting Old Local Git Branches–#SQLNewBlogger
I had a lot of local branches for a repo (actually a few repos). I know these are old and not used anymore, so how do I delete them? This post shows how to do that on Windows.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. You can see all posts on Git as well.

The Problem

As I’ve been making changes for various SQL Saturday events

I saw this SO post, which was a good starting point. I grabbed this code, which I’ll explain below.
```
git fetch -p && git branch -vv | awk '/: gone]/{print $1}' | xargs git branch -d
```
The problem is this doesn’t work on Windows.

Running This on Windows

I assume most of you installed Git and have Git Bash. The xargs and awk commands are Unix/Linux ones, so you need a bask shell to tun them

The solution for me, was to open a bash shell in the repo with the right click menu on Windows.

Then run the code:

Local branches removed. Well, almost; read the next section.

Additions

Note that in the first execution, I had two errors noting that there were some unmerged branches. When I look at these, I see they were old branches, ones that haven’t been used in years. I’m guessing either I was fixing something for someone, or they fixed something in another branch.

So, I forced delete by re-running the command a capital D.

How this Works

This code uses some Unix based utilities that I haven’t used in a long time. The flow of this is similar to how PowerShell, or even VBScript works, but on a single line. In this case, this code:
- Gets a list of branches from the remote with git fetch after pruning the references for local branches that don’t exist on te remote.
- Run the branch command with the verbose output. Could be –verbose as well
- Take the output if the previous step and pipe that through awk. This command will parse text, looking for “gone” in a line and then printing the branch name.
- This text is then taking with xargs and passing it to the git branch command with the delete option.
Note this doesn’t force delete branches.

SQL New Blogger

This post took about 20 minutes to write. I spent about 5 minutes checking a few code examples online, and then tried one after I’d killed branches from GitHub. I don’t have a great solution there, but I don’t do this often and I can click a few buttons to manage this.

I then structured this post with a few screenshots and spent 15 minutes working on it. I’d actually sketched it in 5 minutes with the major sections and a sentence in each and realized this would be quick to write, so I just filled it in on a Sunday morning.

You could do this as well and give an interviewer something to ask you in the next interview. This might catch their eye. I’d also suggest (and I will) do a few posts on awk and xargs. Those are good skills to have and you might spent 20 minutes experimenting and having fun with them.
July 8, 2024
Set a Runtime Variable in an Azure DevOps Pipeline

Note: I DO NOT recommend this. Any changes to a pipeline should be in code and through a PR.

That being said, I know this information is out there and some people need it. The question from a friend was how can they set a variable in an Azure DevOps Pipeline at runtime. This was for testing, and they wanted to change the pipeline behavior to test things when they ran them.

This post will show how to do this in classic and YAML pipelines. As a scenario, I’m just going to get a directory listing of a folder, and change that at runtime.

Classic Pipelines

I know the trend is everything in code. For experimenting and learning, I find this slightly annoying, so I like classic pipelines. I know others do.

In a classic pipeline, I can set variables. I’ll add a new one and call it myLocation. Over on the far right, there is a checkbox for “settable at runtime”. Check that.

Now, I’ll add a task to this pipeline that runs a dir, using this variable.

I can save and run this, and I see the results of c:\Users from a hosted agent.

That’s the default behavior.

Now, let’s alter this at runtime. When I click “run pipeline, I see this on the right side as a blade. Note the “variables” section below.

I can click this and see my variables. System.debug is set at runtime by default, but I see my other one.

If I click this, I can change the location. I’ll set this to c:\.

When I let this run, note I get different results.

I’ve changed behavior at runtime.

YAML Pipelines

In a YAML pipeline, I don’t have tabs or variables. Instead, I just get a script of sections, like this.

I can alter this to add a variable by looking in the upper right, where I see a “variables” button. Click this.

I get a list of variables, which is none in this case. I’ll click “New variable”.

This gives me a dialog where I can enter the information. Note I can set a default as well as let users override this with a checkbox.

When I save this, I see my variable.

Now, I can alter my script. I’ll add this as $(myLocation), where I surround the variable name with a $ and ().

I can validate and save this, which I do.

It’s valid, because I typed well, but this really should go through a PR. Since I’m testing, and I’d approve the PR, I’m doing it in main. I shouldn’t do this in any org.

Now when I run the pipeline, I have the variables item where I can change the variable.

I can also set this variable in YAML, like this:

However, if I set that value, I can’t change this at runtime. Here’s the runtime screen.

I can use a parameter instead. I’ll use this structure:

When I run this, I see a new box:

I can override this “Dir location”. When I set this to c:\, I see these results:

Summary

I’ve shown how to configure a variable to be set at runtime, both in classic and YAML pipelines.

Note, this a place an administrator can make a mistake, or run rogue, without review. This is not recommended. Put all pipeline changes through a PR.

July 5, 2024
A New Word: Symptomania

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

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

July 5, 2024
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