Voice of the DBA

Tag: administration

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

    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.

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

  • What Metrics Do You Collect?

    One of the hot terms in software these days is observability. There are a few definitions (Splunk, RadixWeb), but essentially this is the insight into how your software runs and performs using metrics, logs, traces, etc. In DevOps, we do this with an eye toward improving performance and identifying the root cause of issues. The focus is slightly different from monitoring, where we often focus more on resources and health. We need both, but often in trying to improve software and the behavior for users, developers need observability. Infrastructure people responding to acute issues and looking to ensure we have the capacity, availability, and other x-bilities, that need monitoring.

    Today I’m wondering if you collect a variety of types of metrics for your software that might tell you how your system is running. What things are important to you in order to better serve your clients? If you’re a DBA/sysadmin, what is important to you? If you are a developer, are there different types of data you want?

    Certainly, you might collect various resource measures (CPU, IO, reads, etc.), but there are many more things. There are logs, which could include the SQL Server error log, but I’d hope that you had a more in-depth way of measuring the activity on your system. Do you have custom xEvent traces running? Can you collect application logs easily when you’re looking at issues? Do you spend time trying to solve chronic issues? Do you look for potential future problems?

    Most software applications should include some sort of basic logging of major functions, but I would hope that there are various levels available. If problems are reported or noticed, can you increase the detail of logging? Can you correlate this with logs from different systems, such as the database? Can I get execution data that matches calls and trace down the potential issues that users are experiencing?

    I know that we have problems in applications. Some (many?) of these are data-related, which might be easy or hard to trace down. That often depends if the data changes too quickly or is static enough for someone to investigate a report. Some errors are logical code errors, which might indicate a lack of testing early in the software process, but we ought to be able to determine this quickly from logs. If there are performance issues, and we have a lot of these, how easily can we verify a problem?

    Let us know what types of metrics help you solve issues. I certainly think that you should have some sort of monitoring and observability system in place that helps you dive deep into the database, especially concerning execution plans at the time of the issue. The situation can change quickly inside a database, so capturing data regularly is important. If there is something you wish you had, let us know as well. Maybe someone else will have a neat solution for you.

    Steve Jones

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

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

  • DBCC CLONEDATABASE is Semi-Discontinued

    I saw a tweet that DBCC CLONEDATABASE was being discontinued for production databases, which both scared me and didn’t make sense. I’ve used this a few times for a quick copy of a database and like how it works. Discontinuing it seemed strange to me.

    Then I read the blog post, which notes that it’s not being supported for production deployments. The post doesn’t explain why, but I’m guessing this is because all the stats and other metadata moves, and this might mess up the optimizer if different data is added. I don’t know who deploys production databases like this, but I could see people who have federated or sharded databases using this to create a new blank copy and then uploading data into it. Or maybe people who need new databases that are distributed onto remote office/edge devices used it? If you use this to create production dbs, let me know.

    This command will still be used for generating schema-copies for diagnostic and troubleshooting purposes, which is what it’s there for. I assume this means Microsoft Support will support you using this to investigate strange query issues or if you create a database at their request. However, there isn’t a mention of this being used for development and test environments, which is where I use it.

    Specifically, I’ve used it quite a bit lately with Redgate’s subsetter utility. I need a target that’s shaped like the source, and dbcc clonedatabase gives me that. I don’t really care about stats or anything else, I just need schema set up to move data around. It’s useful there. It’s also useful for a quick test of a deployment, where I can ensure I get the latest production schema and then run a deployment against it (hopefully using Flyway), looking for errors.

    If you use this command, don’t worry. The tool isn’t going away, and the restriction against production copies doesn’t take place until March 1, 2025. That’s nearly a year from now to change your process. If you’ve never used it, well, I don’t have a good reason to start using it, but you ought to be aware of how it works. Learning about new features is handy, as you might discover a problem the knowledge can solve. However, if you don’t know it exists, it will never enter your mind as a solution.

    Be curious and try things. It’s easy, and it’s fun.

    Steve Jones

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

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