February 20, 2007 at 7:01 am
I have been using a maintenance plan in SQL Server 2005 to:
1.) Perform integrity checks on the database.
2.) Perform a full backup of the database
3.) Cleanup old database backup files
4.) Cleanup old log files.
The log file for a successfully executed plan only contains the following:
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.1399
Report was generated on "UTILITY2".
Maintenance Plan: CitrixDatastore Backup
Duration: 00:00:00
Status: Succeeded.
Details:
Is there a way to get more details to be written to the log file about what each step is doing?
Thanks for your help,
Dan Herron
February 20, 2007 at 9:52 am
If you break your job up and setup seperate steps for each part, you can query against each step for success for failure.
I run the following script against my production jobs to let me know the status of all of my nightly jobs were
select distinct [name] as 'Job Name',
case [enabled] when 1 then 'Enabled' else 'Disabled' end as 'Enabled',
cast (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Last Run',
step_id
as Step,
case [h].[run_status]
when 0 then 'Failed' else 'Success'
end as 'Status' ,
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':') as 'Duration',
cast (ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Next Run'
from msdb.dbo.sysjobs j
left join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
join msdb.dbo.sysjobhistory h on j.job_id = h.job_id
where step_id = 0
and j.name != 'SQL Performance Trace'
and h.instance_id in (select max(sh.instance_id)
from msdb.dbo.sysjobs sj
join msdb.dbo.sysjobhistory sh on sj.job_id = sh.job_id
where h.step_id = 0
group by sj.name )
good luck
EP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply