Voice of the DBA

Tag: administration

Fixing DBCC CloneDatabase Dup Key error in sys.sysschobjs
This was an interesting error, and I was able to duplicate it, so I decided to write a post on how to find the problem and fix it. The error after running DBCC CLONEDATABASE is:
```
NO_STATISTICS and NO_QUERYSTORE options turned ON as part of VERIFY_CLONE.
Database cloning for 'atest' has started with target as 'aSmallTest'.
Msg 2601, Level 14, State 1, Line 11
Cannot insert duplicate key row in object 'sys.sysschobjs' with unique index 'clst'. The duplicate key value is (885578193).
```
The final key value (885578193) for you might be different, but the error is the same.

Note: In SQL Server 2022 RTM + GDR, this error occurs with system objects collisions. Upgrading to CU12 fixed this. Possibly earlier CUs fix it, but that’s all I’ve tested.

The Scenario

I connected to a SQL Server instance and ran this:

DBCC CLONEDATABASE(aTest, aSmallTest) WITH VERIFY_CLONEDB;

I was just trying to copy a database to do some testing against a copy. The command too quite a few seconds (11 for me) to run before returning the error above. You can see the screenshot below.

Strange. Why would a copy of a database cause an error here? I’ve run DBCC CLONEDATABASE on this instance before and it worked.

I’m not sure of the exact problem, and my searches note that

The Fix

I found a post that describes a similar issue, but certainly isn’t the case here. Another post from Pinal shows how to query sys.sysschoobhs, which isn’t reachable with a DAC connection. I finally found in the docs that SQL Server doesn’t support cloning with objects in the model database.

So, I need to delete objects in the model database. In my case, I took this query (from the first link above) and ran it from the source database. That’s important. Running from anywhere else doesn’t work.
```
SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN sys.sysobjects c
ON m.id = c.id
JOIN sys.objects o
ON c.id = o.object_id
WHERE --o.is_ms_shipped <> 1
m.name <> c.name
AND m.id IS NOT NULL;
```
As you can see below, this returns two objects.

If I look in model, I see these, one if you just look at tables, but the PK is attached.

If I delete these two objects, then DBCC CLONEDATABASE works.

Summary

This is a strange error, and I’m not sure why it appears, but the documentation notes that running dbcc clonedatabase with objects in model is not supported. I suspect this is a change across one of the CUs, as I know this used to work.

In any case, the fix is remove the objects in model. If you really need these, then I’d create a script to remove and add those objects back, with a call to dbcc clonedatabase in the middle.
July 15, 2024
An Upgrade Slog

I saw a blog post from Randolph West recently that asked How do you restore a SQL Server 2000 database in the year 2024? It’s a bit of a process, involving an intermediate version and two restores. Randolph also points out the need to run DBCC after the first restore, which is a good idea. I wonder how many people would take the time to do this, or even think about it as an upgrade step?

This was interesting to read as I had a customer ask me about doing this a few months back. They were trying to clean up their database estate and modernize some of their older systems. This was becoming a big project for them, as they had several pre-2017 systems, none of which were in support. Auditors, regulatory authorities, and even business partners see this as a large security risk and get concerned if you’re running older software.

I’ve felt that in most cases, I ought to be able to run a database server for close to a decade. I certainly need to patch it with CUs in that time, but the support lifecycle says that you get mainstream support for 5 years and then extended support (paid) for 5 more. That extended cycle also includes security patches, so ten years seems reasonable.

As a side note, the final support lifecycle for 2014 ends on 9 Jul 2024. That’s a decade if you upgraded in the first year of release.

However, many of us have multiple instances, and upgrading those can be a chore. Perhaps you trust that nothing breaks, but I would say for many larger organizations, upgrades are a constant fact of life, and it is important to probably start testing upgrades at five years, knowing it might take 1-2 years to upgrade all instances of a given version. That’s if you don’t find issues in testing. If you test a 2017->2022 upgrade now and find issues, you might spend time mitigating these, or maybe wait for SQL Server 2025 (my guess) and hope you don’t have the same issues. There are also the challenges of in-place vs. side-by-side upgrades, and you might choose one in testing, but decide to change for the final upgrade for various reasons. All those things can cause delays.

I still find myself a little nervous about the “evergreen” versions of SQL Server, where Microsoft patches them as needed. I know they try hard not to break any backward compatibility, but if they do, then you’re stuck. I prefer to schedule my upgrades and make them a normal part of the DBA job. That being said, don’t drag them out for years and years. If you still have SQL Server 2012 or older versions, you’re doing something wrong.

Steve Jones

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

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

July 8, 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
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