Maintenance plans in MS SQL 2008

This article was migrated from an older iteration of our website, and it could deviate in design and functionality.

Maintenance plans in MS SQL 2008 is a really neat feature in MS SQL that is very easily missed. I also found a typical, but not so clear error while starting to use these features.

Estimated read time : 5 minutes

Jump to

A really neat feature in MS SQL is the maintenance plans, that is very easily missed. If you have the standard, enterprise or developer editions of SQL 2008 and the standard Management Studio you can enjoy the full power of these features. The purpose of this article is to serve as a heads up for the features that this offers, but also provide a nice solution to a very common problem that occurs when executing. At least this is something that we experienced and a few others I have found :).

Where can you find the features?

You find these features by opening the Standard MS SQL Management Studio. Then look under the folder "Management", then you will find what's shown in the image below.

image

 You could probably do this via the console or some other cool ways, but that would be a different article.

Features and why use Maintenance Plans

This is a great way to optimize your databases. Clean them or do good things such as backup. Why don't you dwell into the prepared examples then take the plans out for a spin. You can do magic things with them.

Problems when trying to use Maintenance Plans

Here is the first error message that I found in the Event log:

SQL Server Scheduled Job 'BackupPlan.Subplan_1' (0x45C3E2AB1CFA6740AC86DF0C8AC5A62C) - 
Status: Failed - Invoked on: 2010-07-10 19:32:45 - Message: The job failed.  
Unable to determine if the owner (SERVER\BackupAdministrator) of job 
BackupPlan.Subplan_1 has server access (reason: Could not obtain information about 
Windows NT group/user 'SERVER\BackupAdministrator', error code 0x54b. 
[SQLSTATE 42000] (Error 15404)).

This doesn’t really give you all that much, but then I found a great tip to look at the history of the created Maintenance plan. This can be found by right clicking on your plan and selecting “View History”.

image

After searching here I found this result, which gave me much more information, this is what it said:

The SQL Server Execute Package Utility 
requires Integration Services to be installed by one of 
these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation.

What to do to make it work?

Well the error message presented above probably gave you a good hint! Install Integration Services, which can be found by adding a feature to your SQL installation!

On my quest to find the solution for this problem above I also found out that it is important to run the SQL Service under a domain account. Somebody also stated that this is not necessary for the SQL Agent, which could be run on a local account. This also implies that it is quite important to have the SQL Agent running because this is who actually does the work after being triggered by the SQL Service.