Voice of the DBA

Tag: administration

Monday Monitor Tips–Finding CUs for My Instance

How can I quickly get a CU patch for a system that’s out of date? I’ll discuss that situation.

You might think you get to patch every instance every few months, and you may be able to. But most of us have laggards in any decent-sized estate. Someone always wants to avoid patching, or skip patching on the day you’ve scheduled every other system.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

Tracking Versions

The Estate page in Redgate Monitor contains quite a few different views of your entire estate. This section is designed to aggregate data across all the systems you are monitoring. If I look at https://monitor.red-gate.com/Estate/Versions, I see this as a default.

This gives me an overview of what SQL Server versions I’m monitoring. As you can see, our test estate has a mix of versions, from 2008R2 through 2022 and one Managed Instance. The counts are in the pie chart, and we can see how many are up to date in the bars to the right of each version. As you can see, lots of our estate needs patching.

I can also see at a glance for each version that the latest update is and its release date. The download link is a quick way to download the latest patch from Microsoft. We maintain this list and Redgate Monitor will update it on a regular basis.

When I scroll down, I see the details of individual instances. These are grouped, though I can change that with the toggle in the upper left. For each instance, I have the name and the major version,

The current status and the latest patch are listed next to each other, with an icon and color coding that let’s me know I need to upgrade (yellow up arrow) or I’m patched (green checkmark). I once again have the latest patch, linked to the MS article as well as text that let’s me know how out of date I am. You can see these patches are a month old. Auditors are potentially OK with that.

However, for my 2012 instances, I’m way out of date. No excuse for that. We keep these firewalled and protected, and they are available here only for demonstration purposes.

To the right of this we have our support dates. If a date is past, we mark it with a triangle to let you know that you have unsupported versions. That may or may not be an issue for your organization.

Summary

This section of the Estate tab isn’t something I expect DBAs or sysadmins to check often, but I would schedule a reminder to do this quarterly. Knowing the state of our patching process is important, especially when there are security updates being released. We have had a few in the last year for SQL Server, and it is important to patch and apply those.

Seeing not only the status, but having an easy download link makes this a very handy tab that I wish I’d have had in quite a few jobs during the 90s and early 2000s. If you haven’t checked your estate tab in Redgate Monitor, you might do that today.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

September 16, 2024
Saving Emergency Space on my Laptop

With my new laptop, one of the things I realized I’d forgotten to do in setup is reserve some space. I wrote about this years ago, but I wanted to ensure I always had space in the event of an emergency.

Creating Placeholders

In the previous post, I linked to the contig utility from Microsoft. I downloaded this and then copied the new 64-bit utility to my c:\utilities folder, which is where I keep a lot of generic stuff I run.

Then I copied my old command and updated the exe call.

However, I also built a quick batch file to give me 20GB of saved space. I called it create20gb.cmd and it contains this code:

contig64 -n Hold1.place 1073741824
contig64 -n Hold2.place 1073741824
contig64 -n Hold3.place 1073741824
contig64 -n Hold4.place 1073741824
contig64 -n Hold5.place 1073741824
contig64 -n Hold6.place 1073741824
contig64 -n Hold7.place 1073741824
contig64 -n Hold8.place 1073741824
contig64 -n Hold9.place 1073741824
contig64 -n Hold10.place 1073741824
contig64 -n Hold11.place 1073741824
contig64 -n Hold12.place 1073741824
contig64 -n Hold13.place 1073741824
contig64 -n Hold14.place 1073741824
contig64 -n Hold15.place 1073741824
contig64 -n Hold16.place 1073741824
contig64 -n Hold17.place 1073741824
contig64 -n Hold18.place 1073741824
contig64 -n Hold19.place 1073741824
contig64 -n Hold20.place 1073741824

I made a c:\hold folder and dropped the cmd file in there. I then ran it. Here are the results:

Now if I find myself running low on space, I can go in here and delete a few files and get some space back.

I recommend this for all machines, since I’ve run into the space problem so often, usually at a time when I’m stressed and I need space quickly. This makes it easy to get a bit of space back.

September 11, 2024
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