Press "Enter" to skip to content

Category: Administration

Updating SQL Agent Job Owners With dbatools

Published 2018-06-29 by Kevin Feasel

Stuart Moore gives us two methods of updating SQL Agent job owners, one using T-SQL and the other with dbatools:

Now we all know that having SQL Server Agent jobs owned by ‘Real’ users isn’t a good idea. But I don’t keep that close an eye on some of our test instances, so wasn’t surprised when I spotted this showing up in the monitoring:

The job failed. Unable to determine if the owner (OldDeveloper) of job important_server_job has server access (reason: Could not obtain information about Windows NT group/user 'OldDeveloper', error code 0x534. [SQLSTATE 42000] (Error 15404)).

Wanting to fix this as quickly and simply as possible I just wanted to bulk move them to our job owning account (let’s use the imaginative name of ‘JobOwner’).

Click through for both scripts.

Comments closed

Just Update Those Servers Already

Published 2018-06-28 by Kevin Feasel

Randolph West wants none of your excuses:

Folks, we all like to make sure we’re doing our level best to make things work smoothly.

So why am I staring at someone’s server that has never been updated since it was first set up almost three years ago?

Do better, so that I don’t have to yell at you. Seriously.

When we ignore updates, we are ignoring preventable catastrophic problems; we are ignoring fixes to security bugs, performance bugs, and data corruption bugs. Each one of these things could give you a really bad day. In two out of three cases it might even be a career-limiting move.

There are risks to patching servers, but the downside risk tends to be much larger and administrators need to be able to mitigate update risks through redundancy, automation, and having a rollback plan if needed.  It’s more work than not patching, but the outcome is much better.

Comments closed

Missing KB2919355 When Installing SQL Server

Published 2018-06-27 by Kevin Feasel

Ryan Allport explains how to install SQL Server 2016 on Windows Server 2012 R2 when you get the Rule “KB2919355 Installation” failed error message:

As you can see, the upgrade feature rules check failed around the KB2919355 installation. At this point, reading the error message, I assumed (I know, I know, it’s something we should never do as a DBA!) that the patch had been downloaded and applied during the latest round of Windows patching, and all that was required was a server reboot. I was wrong.

Upon running the upgrade again, I got the same error message. Hmm, annoying. So, after some Googling I was confident I knew what to do to resolve this; download and install the KB2919355 patch. So, I downloaded the patch from the official Microsoft website (KB2919355) and kicked off the installation.

There’s a bit more to it than “install the patch.”

Comments closed

Scripting Maintenance Mode Tasks

Published 2018-06-22 by Kevin Feasel

Jamie Wick shares some hard-earned knowledge regarding scripting out maintenance tasks using Powershell:

Given that we have several hundred servers (and growing), this process is taking an increasing amount of time each month. Over the years we’ve implemented various automated patching systems (WSUS, IBM BigFix, etc.) and they’ve worked reasonably well for managing the Download & Install step. The pain point lately has become the first two steps (snapshots and maintenance mode). Both processes are simple to complete using the VCenter web-based user interface and SCOM console. The problem is the volume of button clicks it takes to complete the process for ALL of the servers. Using the standard (web) user interfaces, over an hour of the monthly maintenance window can be lost to just getting the snapshots and maintenance mode tasks completed. Extrapolate that out over a year and we’re looking at over 1.5 DAYS of work-time lost to getting the servers ready to START applying updates. That’s not a statistic we want to publish to senior management. So, how to fix (or minimize) the problem? The answer to which is: Script It.

Let’s take a look at how to use PowerShell to automate the snapshot and maintenance mode tasks.

Read on for sample scripts.

Comments closed

Azure Without ARM

Published 2018-06-21 by Kevin Feasel

Ed Elliott gives us a few ways of deploying Azure resources without using ARM templates:

So, what are our options?

  • Create/Edit/Delete ourselves using Powershell/.Net/Python/Go/Java/Some Other SDK

  • Process something else (YAML?) into JSON

  • Generate the ARM using c#/Powershell/something else

  • 3rd party tools, (Terraform is the big daddy) / others include Sparkle Formation

To be honest, I’d probably just stick with ARM templates.

Comments closed

Checking A Drive’s Allocation Unit Size

Published 2018-06-20 by Kevin Feasel

Ryan Adams shows how to find the allocation unit size for a disk volume:

To identify the allocation unit size for a volume, we can use the fsutil.exe utility.  In the output you are looking for “Bytes Per Cluster” which is your allocation unit size. Here is an example to retrieve the information for the G:\ volume.

1
fsutil fsinfo ntfsInfo G:

Ryan also shows how to change the allocation size, should you need to do so.

Comments closed

“Server Is Configured For Windows Authentication Only” Error

Published 2018-06-19 by Kevin Feasel

Kenneth Fisher diagnoses a misleading error:

In general, the errors SQL gives are highly useful. Of course every now and again you get one that’s just confounding. The other day I saw the following error in the log:

Login failed for user ”. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

This one confused me for a couple of reasons. First, the user ”. Why an empty user? That’s not really helpful. And second Server is configured for Windows authentication only.

But Kenneth shows that the server is configured for SQL authentication as well as Windows authentication.  Click through to see what gives.

Comments closed

Why Your Transaction Log Is Full: LOG_BACKUP

Published 2018-06-18 by Kevin Feasel

Jen McCown explains why you might get the error message “The transaction log for database ‘<your database>’ is full due to ‘LOG_BACKUP'”:

Your transaction log is full. Both Microsoft, and about 100 articles and blogs have covered this topic, but let’s take a quick look anyway. Because, you know, it comes up all the time.

Summary:

  1. This error message points to a lack of log backups.

  2. Make sure using sys.databases.

  3. Start backing up the log.

  4. You can shrink the log if necessary.

  5. A note on SIMPLE mode, and why it’s often a terrible idea.

This is a good summary of the problem and various solutions.

Comments closed

Preventing Server Manager From Loading

Published 2018-06-15 by Kevin Feasel

Steve Stedman shows how to prevent the Server Manager app from loading whenever you RDP into a Windows Server machine:

If you frequently connect to many different SQL Server as I do, you are probably used to the Server Manager loading slowly when you log in with Remote Desktop.

The Server Manager has a bad reputation for taking up lots of CPU over time and possibly even bogging down a SQL Server when left open for days on end.

To prevent this from automatically loading you can do the following to quickly disable it for your user session, and your future user sessions.

Read on for the steps.

Comments closed

Listing SQL Server Instances On A Server

Published 2018-06-12 by Kevin Feasel

Lori Brown has a script which lists installed SQL Server instances:

It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on a set of scripts that we can use for discovery on new systems, I thought it would be handy to have a query that would give me a list of SQL instances with the version that are installed.

I found that this kind of information can be found in the registry which is accessible via a couple of undocumented extended procedures. Let me give the disclaimer right here…. Undocumented procedures from Microsoft are not supported and should not be used for regular production work. This is because they can change or be removed at any time. Now that I have stated that, the other obvious statement is that undocumented procedures can be pretty handy for some things, just use them with caution.

Click through for the script.

Comments closed