Backup schedule and SQL Agent jobs
-
Another question. I read everything I could on backups and that is a lot
....I could not find one article or book chapter on a best practice of running SQL Agent jobs for backups without using the maintence wizmo for backups. I found all sort of other stuff, but not that. I want the control of knowing exactly what backup is running when. I also don't want to pollute msdb. The question I have is on timing. I thought I would use 3 jobs and 3 devices for each database. #1 Full B/U Sunday at 12:00 am using device #1.
Log is backed up with init on next job step. This file is between currently 20GB, but will grow to 30-40GB in size, before I can trim it by deleting records, reindex etc. The b/u should take no longer that 30-45 minutes.
#2 Diff B/U Mon-Sat at 12:00 am using device #2.
Log is backed up with init on next job step.
#3 for Log backup every 30 minutes from 1 am to 11:30pm using device #3
If for some reason full or diff job takes longer that 1 hour, by default Log b/u should fail and will get re-run in 30 minutes
-Or-
Am I better off just writing some T-SQL to determine what day it is with If-then-else logic?
Any problem with this setup?
Thanks,
--Frank
-
I am not clear with your question. Do you want to know if the schedule is ok to be implemented or do you want to have a script that will list the status of the job. If you want this then use the script below.
SET NOCOUNT ON
go
use msdb
go
SELECT convert(varchar(30),dbo.sysjobs.Name) AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Schedule Enabled' = CASE dbo.sysjobschedules.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Occurs Every'=freq_subday_interval,
'Sub Freq Interval'= Case freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration',
'Last Run Status'=isnull(Case when Q2.run_status =1 then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time = 0
UNION
SELECT convert(varchar(30),dbo.sysjobs.Name) AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Schedule Enabled' = CASE dbo.sysjobschedules.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Occurs Every'=freq_subday_interval,
'Sub Freq Interval'= Case freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration',
'Last Run Status'=isnull(Case when Q2.run_status =1 then 'Succeeded' when Q2.run_status=0 then 'Failed' End,'NA')
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,'Last Duration'=T0.run_duration from sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON dbo.sysjobs.job_id = Q2.job_id
WHERE Next_run_time <> 0
-- ORDER BY [Start Date],[Start Time]
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply