Press "Enter" to skip to content

Category: Administration

Automating Database Restorations

Published 2020-09-10 by Kevin Feasel

Hugo Kornelis walks us through automated restoration of database backups:

Now I’ve been to quite a few conferences. And I’ve heard a lot of DBAs talk about best practices. One of the constants in those talks is: automate your restores. So I felt confident that a quick internet search would surely be enough to find me an existing script for restoring a database. Sure, I’d need to modify it to restore to a test database, but that should be minimal effort.

To my surprise, I was unable to find a script. Is scripting this so easy that every DBA can do it with their eyes closed, and nobody feels a need to share it? Is it so hard that they all decided they’re sitting on gold and will only share it for big money? Or were my internet search skills simply severely lacking?

Anyway, bottom line is that I had to do it myself. And I’ll share the result in this post.

Click through for the script and a detailed explanation of how it works.

Comments closed

Automate sp_whoisactive Runs

Published 2020-09-09 by Kevin Feasel

John McCormack shows how to save the output from sp_whoisactive for later research:

When I took over a fairly troublesome SQL Server, I was always being asked what was running when the SQL Server was slow. That was fine when it was during the day and I could look. But often I would be asked, why was the server slow at 9pm last Friday? Or some other time. It’s not always the easiest thing to do as SQL Server doesn’t natively keep this information for you.

A quick but effective way to automate this is to download and schedule Adam Machanic’s sp_whoisactive stored procedure. I run this every 10 minutes but I have seen it scheduled as often as every 30-60 seconds. It depends on your system and your needs.

This is in place where I work, and I’ve put it into place when consulting for companies without the budget for fancy tools. I really appreciate that Adam Machanic made it a simple option to insert results into a table. Also, John has a step to delete older data, which is critical.

Comments closed

Ensuring Trace Flag Consistency Across Instances

Published 2020-09-08 by Kevin Feasel

Taiob Ali creates a process to track ensure specific trace flags are in use:

Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.

My team members fixed this issue by leveraging startup stored procedures.

Click through for the explanation.

Comments closed

Restoring the Master Database

Published 2020-09-03 by Kevin Feasel

Kenneth Igiri walks us through restoring the master database in SQL Server:

The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database.  It also contains most of the dynamic management views that are necessary to monitor the instance.

The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.

It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance. 

It’s definitely not as easy as restoring other databases, but it is possible.

Comments closed

Dealing with Failing SQL Agent Jobs

Published 2020-09-02 by Kevin Feasel

Garry Bargsley has started a four-part series:

SQL Server Agent Jobs are one of the core features of SQL Server. Agent Jobs perform many actions, from maintenance tasks (server and database), data movement flows, replication, log shipping, data cleanup, health checks and many more. Since Agent Jobs are a critical component in a data organization, it is important to know when Agent Jobs do not succeed. There are several ways to accomplish the monitoring of failed Agent Jobs, from easy to more complex. This four part series is going to cover how you, the DBA, can be notified of failing Agent Jobs in your SQL Server environment.

Click through for part one, which is all about finding failed jobs and filtering down to relevant jobs using dbatools.

Comments closed

Error Logs with Windows Containers Running SQL Server

Published 2020-09-01 by Kevin Feasel

Jamie Wick walks us through several locations for error logs when you’re running SQL Server on a Windows container:

So far this series has covered: installing Docker for Windows, the basic commands for managing images and containers, and creating a new image. This post will cover troubleshooting containers & the Docker application using the various log files that are available.

Depending on the type of process being run in a container (interactive or non-interactive), event and error information may be collected by the host logs, application logs, or by the Docker logs (or by all 3, in the case of SQL Server and IIS for Windows).

Read on, as it’s not just “The same places as you’d see on SQL Servers outside of containers.”

Comments closed

Changing Azure SQL DB Service-Level Objectives

Published 2020-08-31 by Kevin Feasel

Monica Rathbun notes that SSMS lets you change service-level objectives for Azure SQL Databases:

Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal.

Read on to learn how, as well as what you can change.

Comments closed

Creating a Fail-Safe Agent in SQL Server

Published 2020-08-26 by Kevin Feasel

Garry Bargsley wants the buck to stop somewhere:

Did you know it is possible for SQL Server Agent to  alert you of problems if something goes haywire with your Agent? Have you ever had an issue with Alerts not being sent after critical events? Then you might need to configure the SQL Server Agent Fail-Safe Operator to save the day. A Fail-Safe WHAT you might say?? This is a special SQL Agent Operator configured in the SQL Agent Alert System in the chance any of the following situations occur.

Click through for the situations as well as configuration steps using Powershell + dbatools.

Comments closed

Cleaning Up the SQL Server Error Log

Published 2020-08-19 by Kevin Feasel

Garry Bargsley does some spring cleaning:

If you are like me, you inherited variously configured SQL Servers when you took over as the DBA for your company. After almost two years, I have gotten all the standards in place where I feel that the environment is clean. One of the last things I accomplished was to standardize SQL Server Error Log configurations, Error Log Cycle schedules and cleaning up of old Error Logs.

To accomplish this there were several steps involved to get all SQL Servers into a unified set of configurations.

This is pretty easy to do and to script out.

Comments closed

Configuring Perfmon for Ongoing Data Collection

Published 2020-08-18 by Kevin Feasel

David Klee killed the radio star:

I just released a new training video (one of many to be released in the upcoming months) showing you how to set up Windows Perfmon for ongoing 24×7 collection. Recording ongoing performance information is vital to have a running history of the system state in case issues arise. More importantly, having a running stream of performance information gives you a running history that you can refer to for generating an ongoing baseline of your system’s resource consumption. Come learn how to set up Perfmon for ongoing collection!

Click through for the video and check out the Heraflux channel.

Comments closed