SQL Server Maintenance Task
I have configured a maintenance task that consists of only two T-SQL Statements, one that executes after completion of the other, however it fails and no history is generated. Please advise how I should begin troubleshooting the issue with the maintenance
task.
April 28th, 2015 1:02pm
what version\edition of sql are you using? how are you executing your maintenance plans ?? through sql job or straight from maintenance plan.
make sure your sql server agent is running. you can get the error log from two places 1. maintenance history 2. job history. these two are integrated..
try the queries below...
if the plan was deleted and recreated, the previous history is gone.
--maintenance plan history use msdb go select case when d.Succeeded=1 then 'Success' when d.succeeded=0 then 'Failed' End as Result, name as [PlanName],b.subplan_name,D.line1,D.line2,D.line3,D.line4,D.line5,D.start_time,D.end_time,D.command,d.Succeeded from sysmaintplan_plans a inner join sysmaintplan_subplans b on a.id=b.plan_id inner join sysmaintplan_log c on c.plan_id=b.plan_id and c.Subplan_id=b.subplan_id inner join sysmaintplan_logdetail d on d.task_detail_id=c.task_detail_id --where a.Name='yourmaintenanceplanname' ORDER BY D.start_time asc GO --maintenance plan history from jobs select case when b.step_id=0 then 'Final Job Outcome' else 'Job Step '+cast(b.step_id as varchar(20))+':'+b.step_name End as [JobOutCome],d.name as [JobName], c.name as [MaintenancePlan],a.subplan_name as [SubPlan_Name],run_date,Run_time,Run_duration,message from dbo.sysmaintplan_subplans a inner join sysjobhistory b on a.job_id=b.job_id inner join sysmaintplan_plans c on c.id=a.plan_id inner join msdb.dbo.sysjobs d on d.job_id=b.job_id --where c.name='yourmaintenanceplanname' order by b.instance_id asc,step_id asc
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2015 5:02pm
You need to provide more information, when you say fail can you tell us what error message is coming. What task is done by these MP.
Did you saw job history
April 28th, 2015 11:32pm