How to create maintenance plan in SQL Server?

sql-server maintenance
nectar picture nectar · Jul 6, 2010 · Viewed 16.6k times · Source

When I'm trying to create a new maintenance plan in SQL Server, it is showing this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

'Agent XPs' component is turned off as part of the security configuration for 
this server. A system administrator can enable the use of 'Agent XPs' by using   sp_configure. 
For more information about enabling 'Agent XPs', see "Surface Area   Configuration" in 
SQL Server Books Online. (ObjectExplorer)

Answer

Martin Smith picture Martin Smith · Jul 6, 2010

You need to enable the SQL Server Agent extended stored procedures first by running the following.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Related questions

How can I copy data records between two instances of an SQLServer database

I need to copy some records from our SQLServer 2005 test server to our live server. It's a flat lookup table, so no foreign keys or other referential integrity to worry about. I could key-in the records again on the live …

Read More
SQL Server xp_delete_file not deleting files

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days. Here's what I've got that's not working: DECLARE @DateString CHAR(8) SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1) EXECUTE master.dbo.…

Read More
Syntax check all stored procedures?

i want to ensure that all stored procedures are still syntactically valid. (This can happen if someone renames/deletes a table/column). Right now my solution to check the syntax of all stored procedures is to go into Enterprise Manager, …

Read More