Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server.

Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy either, as it would be if there were legacy-mode databases on the server. I am certain this is SQL Server 2008 R2 and not Express. I have checked the version and verified the splash screen when studio starts are not Express. I am logging in as the SQL account sa, and the account does have the sysadmin server role.

Lastly, right clicking on a database in SSMS and navigating to "Tasks" does not show any Maintenance Plan option.

Clearly, I'm missing something, and Google is coming up empty considering the scenario I've outlined. Any ideas? Thanks.

EDIT: select @@version returns "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)"

share|improve this question

closed as off-topic by Jack Douglas Nov 14 at 20:57

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

5  
So, it says it is the Express Edition with Advanced Services. I believe it doesn't come with SQL Server agent,or the ability to run maintenance plans or jobs. –  Peter Schofield Jul 19 '11 at 14:18
1  
Yes, thank you. I pasted the edit in, saw an email from a colleague, and realized the answer was right in the version. I'm a doof. –  Tohuw Jul 19 '11 at 14:25

4 Answers 4

For me the problem was that the SQL Server Agent wasn't running on the server. Open the SQL Server Configuration Manager and make sure SQL Server Agent is running. Before doing that you may have to open it's properties and set the Start Mode to Automatic and save the changes.

share|improve this answer

THE STANDARD EDITION, To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role. You can find more information on this subject at http://technet.microsoft.com/en-us/library/ms191002(v=sql.105).aspx

share|improve this answer

Run and check the following:

USE MSDB
SELECT * FROM sysmaintplan_subplans
SELECT * FROM sysmaintplan_plans

If "sysmaintplan_plans" is empty check if the folderid for the maintenance folder is 08AA12D5-8F98-4DAB-A4FC-980B150A5DC8

select * from sysssispackagefolders where foldername ='Maintenance Plans'

If it is not the above id update the folderid like this and new maintenance plans will be shown correct.

update sysssispackagefolders
set folderid = '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'
where foldername ='Maintenance Plans'

It worked for me with an SQL Server 2008 R2

Cheers Peter

share|improve this answer

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent. Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.