Voice of the DBA

Tag: administration

Monday Monitor Tips: Projecting Disk Space

One of the things that many DBAs struggle with is managing space across an estate. There might be one or two servers that you watch closely, or that are a constant problem, but it’s easy to run out of space on other systems.

Redgate Monitor will alert you to low space, but do you want to keep asking SAN admins for more space every week as new systems run out of space? Or do you want to project a number for the year and get that budgeted? Learn how Redgate Monitor can help

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

An Estate View of Storage

The estate view of disk usage shows all your disks totaled up and the projections for growth. This looks like the image below on monitor.red-gate.com. This doesn’t seem useful, but I’ve had storage admins come to me in the past asking about budgeting for the next year. How much space will I use?

The answer is often I don’t know. I’d have to take a very wild guess for them. Now, I’d use this, and maybe pad it slightly, but across lots of systems, we can probably guess on the growth that will be needed. Any one system might not be predictable, but across lots, averages work out.

Here I can see we’re using 25TB (out of 87TB). Definitely be careful showing this to storage admins, as they often want those to be closer, ubt I can see I’m projected to use 91TB in a year, so I’d need more space. The graph above shows me running out of space in Sept/Oct of 2025.

Below this I see details from machines and their disks. These show in the same groups I see on other screens. While I can’t easily filter here, I can search from a browser, or export this information. That’s useful for checking on individual systems.

The other nice thing is I can sort the columns, and if I do this by time until full, I can see the disks that might give me problems soon.

None of the test systems are likely to be problematic soon, but certainly someone needs to budget for next year.

Summary

This is a simple screen, but one that I wish I’d have had in the past. It gives me a lot of information that I rarely need, but information that always requires a lot of effort to compile. What disks are being used by my database servers and what percentage of space is in use.

In a DBA team, this is the type of data I’d want to glance at every quarter and then make adjustments to prevents issues. Proactive DBAs want a screen just like this.

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.

October 28, 2024
Enabling an Index: #SQLNewBlogger
I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.

The Scenario

Imagine that you have an index on a table. In my case, I created this index:
```
CREATE INDEX LoggerNCI ON dbo.Logger (LogID)
```
I can then disable this index with the following code:
```
ALTER INDEX LoggerNCI ON dbo.Logger DISABLE
```
I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.

It’s resume. This code turns the index back on and updates it.
```
ALTER INDEX LoggerCI ON dbo.Logger REBUILD
```
I can also use either of these items:
```
CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING

DBCC DBREINDEX(Logger, LoggerNCI)
```
Interesting short moment the other day as I realized there are a few options here.

SQL New Blogger

While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.

A nice short way to showcase some learning.
October 2, 2024
Cleaning Up Your Database

How many of you have objects in your database that aren’t being used? What about something in a schema with a _old in the name? Or _2 or _3 or _delete? There is a lot of old, deprecated stuff I see in production databases. In fact, I’ve been somewhat amazed as I work with clients that many of the scripts we can build from a database with SQL Compare won’t actually execute on an empty database because the script is full of broken code.

I also find plenty of DBAs that want to clean things up, but they don’t. Sometimes they’re afraid they’ll break something, which is certainly possible. Sometimes they can never find the time. Often they might ask a manager, who usually says this isn’t important and don’t bother.

Is it worth it to clean up your databases?

Brent says no for old code. I say maybe for tables and code.

For a lot of code, Brent is right, your boss doesn’t care and it doesn’t necessarily help you. After all, it’s in production now, and if it’s being used, you’re going to just create problems with a DROP. Where is the business value for removing old code (assuming it isn’t being used)? What benefits do your clients get? Not you being happier there are less objects, but what is the business benefit.

That’s the key. Is there a business benefit. What I’d say is that if you have broken code, it needs to be removed. Because this does impact your software development process, especially when trying to match lower environments. For broken stuff, save the code in your VCS (you do version control database code, right?) and then delete this stuff from prod. It’s broken.

Or fix it.

For tables, I would want to get rid of old tables as well. Why? Well, this is real costs in storage and potential reading of old data. If we moved data to table_old and someone decided they needed to read this for a report at the time, they might still be reading old data. I’d first rename these objects as object_delete_date with the date being a month away. Then I’d set a reminder for that date. On that date, bcp out the data, then drop the table. Period.

Two other things. First, make sure you know how to recreate the table (see the VCS comment above) and bcp in the data. Two, this is low-priority work. If you want to clean the database, know this is a long term, baby step process that will take months or years, and may never end.

Steve Jones

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

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

October 2, 2024
Monday Monitor Tip: Compliance Checking for Old Versions

A customer recently wanted to know if any of their instances were too old and out of support. This was for a compliance purpose, and they had the need to show a report to management of when instances were out of compliance with policies.

This post shows how you can do this in Redgate Monitor.

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

Compliance Templates

This is an Enterprise feature, but it allows the administrators of Redgate Monitor to define templates for how the various servers and databases ought to be configured. Deviance from standards, or compliance with standards is often something auditors care about you documenting.

This is very easy with Redgate Monitor. Let’s see how this works.

Under the Security tab (appears in Enterprise edition), you can see there is a Configuration Compliance item. Click this.

This brings up the Configuration Compliance screen. This starts with Servers (highlighted with a bar to denote this tab, but there are also databases and Compliance templates. We want to choose compliance templates.

This tab shows the various templates that exist already in the system. I’m looking at monitor.red-gate.com, which has three templates configured already.

If you scroll to the right, we can see that two of these are server (instance) templates and one is a database template. We can add a new one, or edit existing ones. For this post, I’ll edit the Workload Server template.

After we click Edit, we get another set of tabs for the template itself. The default is for security, but there are also performance and environment options. Pic the Environment tab.

This brings up a list of settings. Scroll down to the bottom and you will see what settings below for Product Level, Product Version, Product version number, and Edition.

In this case, the template is checking that every server has at least SQL Server 2019 Enterprise installed. If any other version were installed, this would show as non compliant. This is useful for grouping your servers by versions.

The values entered here are the ones you would get back from the ServerProperty() function.

Summary

This post shows how you can configure a compliance template to check that the versions of SQL Server you have installed meet your requirements for a version.

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 30, 2024