Voice of the DBA

Tag: administration

  • Rename a Database: #SQLNewBlogger

    I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Using sp_rename

    I thought sp_rename would work, and sure enough, it did.

    2024-10_0232

    However, I need to object type. If I remove that parameter, it fails:

    2024-10_0233

    The command is looking for an object in the current database by default.

    Technically, I ought to do this to be explicit, naming the parameters.

    2024-10_0234

    I have a better way, however. Note: it’s not sp_renamedb, which is marked for deprecation.

    ALTER DATABASE

    I don’t know when this changed, or if, but you can use ALTER DATABASE to change the database name. There is a MODIFY NAME option for this command that works well. You can see this below.

    2024-10_0235

    This is very clear and seems like better DDL For this process, which can easily be captured as code without worrying about parameters or ordering or anything else. I’d recommend using this.

    SQL New Blogger

    This post took me about 10 minutes to write. Easy. I had done a few experiments and I had code ready (which went to the customer), so I didn’t spend time there. Just rewrote what I did and learned in a few minutes.

    You could do this, add to your blog, and maybe get an interviewer to ask you about this after they saw your post.

  • Monday Monitor Tips: Tracking The Cost of Instances

    One of the things that I’ve been asked in every operations situation is what licenses do we need for our servers. This is a rare request, often once a year, but it results in a bunch of work to figure out what’s running.

    I hate these requests because they always cause delays in other work.

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

    What’s Running?

    Redgate Monitor makes this easy to track (if you’re monitoring all your servers). In the Estate tab, there is a Licensing item. If you check this out, you see something like this:

    2024-10_0192

    This is a quick view of our systems, letting me know what cores we have in service based on the edition. This is a quick report, and if you’re monitoring all prod systems, this gives you a pretty close look at what you need.

    The exceptions here are that we are reporting all cores for all nodes in HA/DR setups, and that might be more licenses than you need. However, you can subtract all the standby nodes if you have Software Assurance on those systems since you do not need to license those.

    Of course, you shouldn’t need to license dev/test systems if you run developer edition, and you should.

    You can filter by groups or other items that can help you focus on part of your estate. This is the standard Redgate Monitor at the top set of filters.

    2024-10_0193

    There also is an “Export” button on this page, so you can save off your data as an Excel XLSX.

    Summary

    This section of the Estate tab gives you a quick view of your obligations to Microsoft for licensing. It’s a nice way to track this and report on it when you need to audit this information without spending a lot of time compiling the information.

    Your Finance group will thank you.

    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.

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

    2024-09_0139

    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.

    2024-09_0149

    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.

    2024-09_0154

    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.

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