March 19, 2009 at 3:54 pm
I would like to be able to run a Maintenance Plan from the "Execute T-SQL" task of another maintenance plan. I found extended stored procedure xp_sqlmaint, but BOL says this is going away and not to use it. BOL does not say if there is a replacement or a workaround for xp_sqlmaint. We have xp_cmdshell disabled as part of our network security. Does anyone know of a replacement or a workaround for xp_sqlmaint that does not involve xp_cmdshell?
Thanks.
March 20, 2009 at 3:26 am
xp_sqlmaint was used by the SQL 2000 maintenance plans.
In 2005 maintenance plans are created as SSIS packages. So instead of using the EXEC T-SQL task use an Integration services package in your job step or use the dtexec command line utility.
[font="Verdana"]Markus Bohse[/font]
March 20, 2009 at 7:30 am
I can't use a job with multiple steps. We want to run multiple DB Maint tasks in sequence (e.g. IntegrityChecks then Optimizations then DbBackups) and if one task fails we still want the following tasks to still execute, but we also want the job to show the failure in the Job Activity Monitor. A multi-step job will not do this, but a maint plan containing the tasks will move on (workflow type of completion) and still throw the error back to the job, which will show the failure. We also cannot use the ExecuteJob task in the maint plan because the maint plan will fire off the ExecuteJob task and not wait for it to finish before moving on. We need one task to finish before moving on to the next because Optimizations interferes with IntegrityChecks and DbBackups. We need to use a task that waits for return before moving on, like the Execute T-SQL task.
March 20, 2009 at 11:29 am
Edwin Frydendall (3/20/2009)
I can't use a job with multiple steps. We want to run multiple DB Maint tasks in sequence (e.g. IntegrityChecks then Optimizations then DbBackups) and if one task fails we still want the following tasks to still execute, but we also want the job to show the failure in the Job Activity Monitor. A multi-step job will not do this, but a maint plan containing the tasks will move on (workflow type of completion) and still throw the error back to the job, which will show the failure. We also cannot use the ExecuteJob task in the maint plan because the maint plan will fire off the ExecuteJob task and not wait for it to finish before moving on. We need one task to finish before moving on to the next because Optimizations interferes with IntegrityChecks and DbBackups. We need to use a task that waits for return before moving on, like the Execute T-SQL task.
Why not just create a detailed Maintenance plan? Like here is a simple one in the attachment?
So in yours you will have ..
Task for Integrity Check
-> Blue Arrow (Success or Failure) Going to Optimization
-> Red Arrow (Failure), Email Alert; with failed information
Task Optimization
-> Blue Arrow (Success or Failure) Going to Backup
-> Red Arrow (Failure), Email Alert; with failed information
Full Backup
-> Green Arrow (Success) To delete Old backups
-> Blue arrow (Success or Failure) clear old backup history from Job History Logs.
-> Red Arrow (Failure, Email Alert; with failed information
and so forth ... You can build quite the logic in SQL Server Maintenance plans now :).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 30, 2009 at 7:11 am
Mays: "Why not create a detailed Maintenance Plan?"
We already do that. We want to get away from that.
We do Optimizations only once per week (during the slowest times for our customers because is causes blocking), whereas we do everything else every day. This means that:
1. You have to use expressions to take the optimizations path only once a week and the "not-optimizations" path every other day. This increases complexity both for having different schedules for different customers and creating new maintence plans with possibly different schedules.
2. It makes the maintenance plan at least a partial schedule controller, which it was not intended or designed to be.
3. If you do not want to use expressions then you have to have multiple and similar maintenance plans with individual tasks covering the same databases appearing in multiple maintenance plans. This means that if a task uses a list of specific databases instead of "all user", "all system", or just "all databases" you need to maintain multiple, redundant maintenance plans whenever you add or remove a database.
That's why we don't want to use a "detailed maintenance plan".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply