Solved

Run Maintenance Plan From Query

Posted on 2006-11-25
7
365 Views
Last Modified: 2012-05-05
I have a mainenance plan
DB Maintnance Plan 2
Is is possible to to execute a query that will run this maintenance plan

if so what is the syntax
0
Comment
Question by:PhilChapmanJr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18011591
Sure - you can run it with
exec EXEC xp_sqlmaint '-PlanID xxxxxxxx' WHERE xxxx is the plan ID.
To identify the plan ID, run:
exec sp_help_maintenance_plan.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18011600
Some references as to the switches that can be passed in, and other usage:

http://msdn2.microsoft.com/en-us/library/ms188408.aspx
http://www.transactsql.com/html/xp_sqlmaint.html
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18011697
Nightman,

if I use
exec EXEC xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
I get a Incorrect syntax near the keywork 'EXEC'.

If i use
EXEC xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
I get a Could not find stored procedure 'xp_sqlmaint'.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 29

Expert Comment

by:Nightman
ID: 18011707
Sorry about the double 'exec' - typo.

The extended stored proc is on the master database, so you would either have to connect to the master database first or run it like this:

EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F'
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18011742
A report is displayed however in the Maintenance Plan there is a backup scheduled ( Not for this date and time ) is there a way to force the backup to run.
0
 
LVL 29

Accepted Solution

by:
Nightman earned 250 total points
ID: 18011753
You must use the switches (documented in the links I posted) to force the specific actions to take place. For example, if you wanted to execute the backup, you would do

EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'

Also, if you are going to do this from the SQLDMO, theoretically (I haven't done it myself) the report should be available in the QueryResults object.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18011759
There is another example of using this approach and storing results in the systables as well
http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question