Use PowerShell to Clean-up SQL Server Jobs

Use PowerShell to Clean-up SQL Server Jobs

  • Comments 1
  • Likes

Summary: SQL Server MVP, Sean McCown, shows how to use Windows PowerShell to clean-up SQL Server jobs.

Microsoft Scripting Guy, Ed Wilson, is here. Today we wrap up the weekend with the last of three blogs by guest blogger, Sean McCown. You can read more about Sean and his previous guest blogs on the Hey, Scripting Guy! Blog site.

Take it away Sean …

In the previous two blogs of this series, Use PowerShell to Work with SQL Server Instance-Level Properties and Use PowerShell to Examine Dirty and Expensive SQL Server Properties, I talked about working with server objects so you could inventory or standardize your environment. Today, I’m going to run through a few operations that you may want to perform on your jobs because they’re some of the objects you’re going to work with the most. And even better, I’m going to show you how to go from a regular line to code to an actual script that you can run your enterprise with. I’m going to go a little faster here than I have in the past two posts because if you’ve read the others, you should be getting better at this.

First let’s pull up a list of the jobs on our box. Start by simply connecting to the provider and navigating to the Jobs node. I’m going to do this by right-clicking the Jobs folder in SSMS, and then clicking Start PowerShell.

Image of menu

Let’s start with a scenario I find myself in from time-to-time. This will demonstrate the power of the Shell better than anything else. We’ve got a situation where an employee left. He owned a bunch of jobs on a bunch of servers, and we need to set his replacement to be the owner of all these jobs. For this exercise we’ll say that there are 10 servers and around 50 jobs on each server. So this is definitely something you wouldn’t want to do by hand.

We’ll start by pulling up a list of jobs on the first server to make sure that our query is correct.

Dir | %{$_.OwnerLoginName –eq “NicC”} ## Notice I’m filtering by only jobs he owns.

Now I need to change the owner to RobertD. Actually, that’s easy enough.

Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”}

And if you remember from the last post, this won’t fly because I’ve only changed the owner in the current Windows PowerShell session, not on the server. Remember, I have to call the Alter() method to make that happen.

Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}

Notice that I’m not only calling the Alter() method, but I’m also printing the name of the job to the screen every time so I’m not just staring at a blinking cursor wondering if anything’s going on. That’s a great start, but I haven’t met my goal yet, have I? No, I still need to make this work on several boxes; otherwise, what’s the point, right?

First we need to get a server list. Because we can’t do this against different servers until we know what those servers are. And because we’re DBAs, I’m going to pull the data from a table where it belongs. So I’m going to get the data from my Servers table and put it into a variable to make it easy to work with.

$ServerList = invoke-sqlcmd –ServerInstance “MgmtRepoServer” –database “MgmtRepoDB” –query “Select ServerName from dbo.Servers where application = ‘ThisGuysApp’”

You can see that I didn’t put a lot of thought into database design, but I chose the object names in the query to be more instructive than anything. However, now we have our server list. So let’s put it into our script and see where that puts us.

$ServerList | %{

Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}

}

We plugged this into our script and it clearly won’t work. We need a couple things to make this work. First, although we manually connected to the server to run our initial job query, we need to have the script make the connection for us. And second, we need that connection to be dynamic. That is, it needs to connect to each of the boxes listed in the $ServerList variable.

We’ll take care of the connection first:

$ServerList | %{

CD SQLSERVER:\SQL\SDLSEANM\DEFAULT\JobServer\Jobs;

Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}

}

As the script reads, when we cycle through the server names in $ServerList, we’re going to connect to the same server again and again. So now we have to take care of the second issue, which is making that server connection dynamic.

$ServerList | %{

$currServer = $_.ServerName;

$currServer; ## Print each server as they come through so you can tell which one you’re on.

CD SQLSERVER:\SQL\$currServer\DEFAULT\JobServer\Jobs;

Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}

}

Now I’ve done what I need to do. I’ve set the $currServer variable to the current server name being passed in by $ServerList, and I’ve replaced SDLSEANM with that variable. Now we’ll connect to each separate server as it comes through our loop.

As far as this situation goes, we’ve accomplished what we set out to do, but I want to give you some advice—and I really can’t stress this enough. Whenever you write a process in Windows PowerShell, always write it to go against several boxes. It costs you nothing. As you can see it’s just a couple extra lines of code, and now you can run your enterprise with it. Even if you’ve only got a single box to run it against right now, you never know when you’ll need to do it against many boxes.

And now that we’ve got the basics down for how to do something useful with jobs on a bunch of servers, let’s look at some other things you can do. The cool thing is that all you have to do is replace the dir line in the script above, and all of these will work as-is. This is how you should be building these types of scripts. Make templates and the specifics fold in nicely. This is a job template, btw.

Let’s plug in another command to change the job category. This is really useful when you want to organize all of your maintenance jobs into the same category so you can filter them more easily.

$ServerList = invoke-sqlcmd –ServerInstance “MgmtRepoServer” –database “MgmtRepoDB” –query “Select ServerName from dbo.Servers where application = ‘ThisGuysApp’”

$ServerList | %{

$currServer = $_.ServerName;

$currServer; ## Print each server as they come through so you can tell which one you’re on.

CD SQLSERVER:\SQL\$currServer\DEFAULT\JobServer\Jobs;

Dir | %{$_.Name –match “Reindex”} | %{$_.Category = “Maintenance”; $_.Alter(); $_.Name}

}

This is a simple example, but it gets the point across. I took all the jobs where the name contained the word Reindex and put them in the Maintenance category.

This brings up another good example though. I had to search on anything that contained Reindex. I shouldn’t have to do that because reindex routines should all have the same names across all your boxes—as much as possible anyway. So to that end, now let’s change the names of all the reindex routines and standardize them so we know what we’re looking at when you go to each box. This will be a simple example again. It’s sometimes not quite this simple, but you can build in your own logic.

$ServerList = invoke-sqlcmd –ServerInstance “MgmtRepoServer” –database “MgmtRepoDB” –query “Select ServerName from dbo.Servers where application = ‘ThisGuysApp’”

$ServerList | %{

$currServer = $_.ServerName;

$currServer; ## Print each server as they come through so you can tell which one you’re on.

CD SQLSERVER:\SQL\$currServer\DEFAULT\JobServer\Jobs;

Dir | %{$_.Name –match “Reindex”} | %{$_.Name = “ReindexAllDBs”; $_.Alter(); $_.Name}

}

There’s no way I could give you a complete solution here because I don’t know what your server looks like, but this is a framework for you to use to build your own scripts for working with jobs.

Now that I have standardized reindex routines I want to disable all of those stupid maintenance plan jobs that do reindexing. I’m going to disable all of the jobs that belong to maintenance plans.

$ServerList = invoke-sqlcmd –ServerInstance “MgmtRepoServer” –database “MgmtRepoDB” –query “Select ServerName from dbo.Servers where application = ‘ThisGuysApp’”

$ServerList | %{

$currServer = $_.ServerName;

$currServer; ## Print each server as they come through so you can tell which one you’re on.

CD SQLSERVER:\SQL\$currServer\DEFAULT\JobServer\Jobs;

Dir | %{$_.Name –match “Subplan”} | %{$_.IsEnabled = $False; $_.Alter(); $_.Name}

}

Depending on the version of SQL Server you’re using, you may have to filter your jobs by other criteria, but this is what it worked out to be on my local box.

You can see how easy it is to build a simple script that will do things against multiple boxes. If you do it right, you’ll have it as a template where you can make minor changes to get new functionality. The way I walked you through building this process is how you should do it every time. Start by getting the basic functionality you need and then expand it little-by-little until you have what you need.

Remember that you may not be able to fully automate everything you do, but that’s OK. I’m also a big fan of semiautomated processes. Anything that gives me back any amount of time is alright in my book. You may not be able to fully automate it with your current knowledge or state of technology, but that doesn’t mean that it’ll stay that way. You may learn something in two months that’ll show you how to do what you’re looking for. So if you cannot currently automate something fully, don’t let that discourage you from doing what you can.

~Sean

Thank you, Sean, for three days of awesomeness. I really appreciate you sharing your time and knowledge. Join us tomorrow for a great blog post by Windows PowerShell MVP, Chad Miller—also about SQL Server and Windows PowerShell. Cool stuff. Trust me.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Nice, informative post, but keep in mind that:

    Dir | %{$_.OwnerLoginName –eq “NicC”}

    will only return "true" or "false", so when you try to modify the owner:

    Dir | %{$_.OwnerLoginName –eq “NicC”} | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}

    won't work. You need to pass the entire job through the pipe:

    Dir | %{ if ($_.OwnerLoginName –eq “NicC”) { $_ } } | %{$_.OwnerLoginName = “RobertD”; $_.Alter(); $_.Name}