Troubleshooting SQL 2000 Maintenance Plans

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:44

Hello and welcome to another SQL Server Video. My name is Michael Campbell, and in this video, we’ll look at ways to troubleshoot backups created as maintenance plans with SQL Server Enterprise Manager.

In a previous video, creating maintenance plans with SQL Server Enterprise Manager, we looked at how to use maintenance plans to create reliable backup routines that serve as a core component of disaster recovery plans.

And during that video, we saw how the plan substeps and details that we outlined were turned into individual schedulable tasks, or jobs, that SQL Server could hand off to the SQL Server Agent for regular execution.

In this video, we’ll look at troubleshooting problems that these jobs encounter while executing.

To get started, we’ll expand the Management node in Enterprise Manager, and then drill into the SQL Server Agent node, where we can see a listing of the jobs currently defined on this server. And, by looking at this listing of jobs, we can immediately see that one of our jobs has recently run into a problem – as the icon for the job is a red x.

Of course, the red-x only indicates a failure the last time this job was run – so don’t assume that just because you don’t see any red-xes that everything is fine – as you may have a job that’s occasionally running into problems.

Therefore, if we want to see if a job has encountered any problems in the past, we just need to right-click on it and select the ‘view job history’ option. From there, we can see a listing of recent execution outcomes.

Of course, while viewing the job history for each of your SQL Server Agent jobs is one way to review overall backup plan outcomes, it’s not the best way to quickly get a feel for how your maintenance plans are behaving – so we’ll look at a better way to do that in just a minute.

For now though, note that when we look at the errors or messages pane at the bottom of this history window, we’re just told that the ‘job failed’ – which obviously isn’t that helpful. And that’s because we’re looking at details provided by the scheduling engine, or SQL Server Agent.

As such, if we want to get additional information about what failed, we’ll want to check the ‘show step details’ option – which will provide us with a new sub-node for each sub step within the job that we’re looking at.

By looking at this information, we’ll frequently be able to find information that gives us a good idea of what kind of error our job ran into while executing. I also find that it’s frequently a good idea to grab any error codes or other details and just paste them directly into Google.

Taking this approach won’t always lead you to a solution, but as you start tracking down different options, you’ll frequently start to get a feel for what some of the probable culprits could be.

Sadly though, a commonly used, or reported, error that you’ll see here is the ‘dreaded’ SQLSTATE 42000 description – accompanied by Error 22029 – which SQL Server uses as a ‘catch all’ error for any number of things that it doesn’t know how to report on.

I’ve seen this error in cases where the account executing the job has somehow lost permissions, or in cases where you’re trying to backup multiple databases and one of the listed databases no longer exists, or in cases where some sort of problem with the database itself prevents you from executing a backup, along with a bunch of other cases that I can no longer remember. The point being, that if you do a Google search on these error codes or details, you’re likely to launch yourself on a wild goose chase.

As such, there are a couple of other troubleshooting tips and tricks that we can look at – both for cases where you run into this scenario, or in cases where you need additional help troubleshooting what’s going on.

And the first of these is to look at some additional history, or logging information – which we can access by right clicking on the maintenance plans node.

Then, if we select the maintenance plan history option, we’re presented with a great little browser that lets us slice and dice all sorts of information on a number of different selectors or criteria up at the top of the browser.

With this tool, we can quickly review overall backup progress for any of our maintenance plans (which provides a much easier way to review history than right-clicking on each job in the SQL Server Agent node), and we can also filter on various nodes to quickly troubleshoot failed jobs.

Then, by double-clicking on one of the entries listed, we can pull up additional information and details that can frequently shed additional light on the subject. And, in this case, this error message helps shed a lot of light on the subject – by telling us that there’s a problem with the database. And, if you’ve been a DBA for long, you’ll know exactly what’s up in this case.

That said, there are some additional tools and tips you can use to help further troubleshoot – for cases where the failure, or problem, isn’t so apparent or obvious.

The first of these tools comes in the form of additional logging or reporting – which can be optionally configured from within your maintenance plans. And, by configuring this option, any steps, or jobs executed as part of this maintenance plan will output additional logging information, in the form of a .txt report, to the directory specified.

In SQL Server 2000, these reports typically aren’t as valuable as they are with SQL Server 2005 and 2008, but they can help give you a good idea of what steps are executing before a failure or problem occurs – especially when executing more complex operations.

In our case, if we navigate to the folder where these reports are located and open one of these text files after the job has re-run with the logging option in place, we’re not going to see much more than what we were already getting with the Maintenance Plan History Browser – but don’t discount these reports when doing your own troubleshooting, as you’ll sometimes find very relevant information here.

And, don’t forget to Google on any error codes that you find in either location – as any specific details you encounter have likely been addressed numerous other times by SQL Server 2000 users over the years.
Finally, another great thing you can do if you’re running into an especially difficult problem is head into the SQL Server Agent, and open up the job that you’re having problems with.

Then, by switching to the steps tab, and opening up the exact step you’re having problems with, you can actually grab the exact T-SQL that is run each time the job is being run.

And, as long as you won’t be introducing any nasty side-effects, you can try running this query directly against the server – though you’ll need to remember that the security context you’re running it under usually won’t be the same context it get run under when it’s executed by the SQL Agent. Still, playing around with this can be a great way to get a feel for what’s going on.

As you can see from the code listed here, what we’re actually doing each time we execute a maintenance plan is fire off a specialized, or extended, stored procedure with a number of different parameters that control the actions of a specialized maintenance application: SQLMaint.exe.

Of course, in our case, executing this code won’t help much – and we actually get fewer details when manually executing it than we do when the job is run by the scheduler, but that won’t always be the case – and by manually executing the code, you’ll frequently be able to shed additional light on the problem.

In our case though, the problem we’re encountering is due to the fact that we’re trying to do transaction log backups on a database that has been set to simple recovery mode – meaning that there isn’t any log file data to backup.

Therefore, by setting the option back to Full, backup operations will be able to recommence without problems. And, accordingly, this is one of the first things I always check when troubleshooting problems with log file backups.

But, hopefully now that you’ve watched this video you’ll have a better handle on how to troubleshoot your own jobs and tasks. And, as you can see, here’s a recap of the things you’ll want to do when trying to troubleshoot your own maintenance plans.

Otherwise, we’re done with this video – thanks for joining, and we hope to see you in subsequent videos.