SQL Server: Programmatically Execute Maintenance Plan

Question

Is there any way to programmatically execute (start) a SQL Server maintenance plan? We have a windows service that runs nightly and updates the DB with lots of data, once that is finished we would like to trigger a maintenance plan in the DB to start running.

Answer 1

You can start a job which is part of your maintenance plan by sp_start_job:

use msdb; 
go
exec dbo.sp_start_job N'job_name' ;
go

Answer 2

If you get the error "Could not find stored procedure 'dbo.sp_start_job'." try this one:

execute msdb.dbo.sp_maintplan_start @plan_id = N'549EDF1B-5712-472E-9722-DD81F622A3C2'

You get the guid from this query:

SELECT s.id AS [ID]    
FROM msdb.dbo.sysmaintplan_plans AS s    
WHERE s.name=N'MyMaintenancePlan'