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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics