August 9, 2005 at 3:24 am
Is there a table that holds/a stored procedure that returns the maintenance plan details. I want to retrieve this information and plot in some form of calendar to determine if the maintenance plans can be re-scheduled to avoid heavy use periods.
August 9, 2005 at 3:29 am
The definition of the maintenance plans are stored in the system database msdb. Take a look at the sysdbmaintplan* tables. I assume you can also use SQL-DMO for it, but I have never tried it. If it is possible it is probably a better alternative.
August 9, 2005 at 4:57 am
Thanks Chris,
Gives all the details I needed from the plan running point of view.
Also found an excellent query at http://www.databasejournal.com/scripts/article.php/1467891
which outputs the plan schedule.
August 9, 2005 at 5:37 am
Great, and nice of you to reply with the url for the query so others with the same issues are helped.
August 9, 2005 at 5:57 am
You might also check out SQL Sentry at http://www.sqlsentry.net for managing your plans.
August 9, 2005 at 8:32 am
That's good for Maint plans, but what about all the other SQL jobs ? I have a procedure to write ALL jobs to a table, then I can run a script to see if/when any of them overlap. This was useful to see what jobs are conflicting with each other, such as jobs running during backups, or poorly scheduled jobs running concurrently when they should be in series. (I think I stole part of this code from somewhere, but I don't remember where ... sorry)
This code is for 1 server. ( Multiple servers: We have jobs on server 2 that can't run until jobs on server 1 complete. For multiple servers, I populate a JobAnalysis table on each server, then DTS them to 1 location, and run a variation of the overlapping code against them all.)
CREATE TABLE [dbo].[JobAnalysis] (
[JobName] [varchar] (55) COLLATE Latin1_General_BIN NULL ,
[RunDate] [int] NOT NULL ,
[StartTime] [varchar] (8) COLLATE Latin1_General_BIN NULL ,
[RunDuration] [varchar] (8) COLLATE Latin1_General_BIN NULL ,
[StartDateTime] [datetime] NULL ,
[run_time] [int] NOT NULL ,
[run_duration] [int] NOT NULL ,
[EndDateTime] [datetime] NULL
) ON [PRIMARY]
-- Populate the table JobAnalysis
truncate table JobAnalysis
declare @forDate_begin datetime,
@forDate_end datetime,
@forDate_begin_char char(8),
@forDate_end_char char(8)
if @forDate_begin IS NULL
BEGIN
SET @forDate_begin = dateadd(day, -1, GETDATE())
SET @forDate_end = GETDATE()
END
SET @forDate_begin_char = REPLACE(CONVERT(CHAR(10),@forDate_begin,120),'-','')
SET @forDate_end_char = REPLACE(CONVERT(CHAR(10),@forDate_end,120),'-','')
insert into JobAnalysis
select /*distinct*/ rtrim(convert(varchar(55),b.[name])) as JobName,
a.run_date as RunDate,
CASE
WHEN substring(convert(varchar(6),a.run_time), 2,5) = ' ' THEN '00:00:00'
WHEN substring(convert(varchar(6),a.run_time), 4,3) = ' ' THEN -- when pos 4,5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':0'
+ substring(convert(varchar(8),a.run_time),1,1) + ':'
+ substring(convert(varchar(8),a.run_time),2,2)
WHEN substring(convert(varchar(6),a.run_time), 5,2) = ' ' and substring(convert(varchar(6),a.run_time), 4,1) <> ' ' THEN -- when pos 5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),1,2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) = ' ' and substring(convert(varchar(6),a.run_time), 5,1) <> ' ' THEN -- when pos 6 is blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),2,2) + ':'
+ substring(convert(varchar(8),a.run_time),4,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) <> ' ' THEN -- when none are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2) + ':'
+ substring(convert(varchar(8),a.run_time),5,2)
end as StartTime,
substring(replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_duration)))
+ convert(varchar(6),a.run_duration)), ' ', '0'),1,2) + ':' +
substring(replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_duration)))
+ convert(varchar(6),a.run_duration)), ' ', '0'),3,2) + ':' +
substring(replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_duration)))
+ convert(varchar(6),a.run_duration)), ' ', '0'),5,2)
as RunDuration
,CONVERT(datetime, (convert(char, a.run_date)), 121) + ' ' +
CASE
WHEN substring(convert(varchar(6),a.run_time), 2,5) = ' ' THEN '00:00:00'
WHEN substring(convert(varchar(6),a.run_time), 4,3) = ' ' THEN -- when pos 4,5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':0'
+ substring(convert(varchar(8),a.run_time),1,1) + ':'
+ substring(convert(varchar(8),a.run_time),2,2)
WHEN substring(convert(varchar(6),a.run_time), 5,2) = ' ' and substring(convert(varchar(6),a.run_time), 4,1) <> ' ' THEN -- when pos 5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),1,2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) = ' ' and substring(convert(varchar(6),a.run_time), 5,1) <> ' ' THEN -- when pos 6 is blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),2,2) + ':'
+ substring(convert(varchar(8),a.run_time),4,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) <> ' ' THEN -- when none are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2) + ':'
+ substring(convert(varchar(8),a.run_time),5,2)
end
as StartDateTime
,a.run_time
,a.run_duration
,CONVERT(datetime, (convert(char, a.run_date)), 121) + ' ' +
CASE
WHEN substring(convert(varchar(6),a.run_time), 2,5) = ' ' THEN '00:00:00'
WHEN substring(convert(varchar(6),a.run_time), 4,3) = ' ' THEN -- when pos 4,5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':0'
+ substring(convert(varchar(8),a.run_time),1,1) + ':'
+ substring(convert(varchar(8),a.run_time),2,2)
WHEN substring(convert(varchar(6),a.run_time), 5,2) = ' ' and substring(convert(varchar(6),a.run_time), 4,1) <> ' ' THEN -- when pos 5,6 are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),1,2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) = ' ' and substring(convert(varchar(6),a.run_time), 5,1) <> ' ' THEN -- when pos 6 is blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),2,2) + ':'
+ substring(convert(varchar(8),a.run_time),4,2)
WHEN substring(convert(varchar(6),a.run_time), 6,1) <> ' ' THEN -- when none are blank
left(convert(varchar(8),replace(convert(char(6),SPACE(6 - len(convert(varchar(6),a.run_time)))
+ convert(varchar(6),a.run_time)), ' ', '0')),2) + ':'
+ substring(convert(varchar(8),a.run_time),3,2) + ':'
+ substring(convert(varchar(8),a.run_time),5,2)
end
as EndDateTime
from msdb.dbo.sysjobhistory a join msdb.dbo.sysjobs b on a.job_id = b.job_id
where (/*step_name = '(Job outcome)' or*/ step_id = 0) and run_date between @forDate_begin_char and @forDate_end_char
order by RunDate, StartTime, RunDuration desc
-- Now Update the END DATE based on START DATE plus DURATION
update JobAnalysis
set EndDateTime = DATEADD(mi, datepart(mi,runduration), EndDateTime)
update JobAnalysis
set EndDateTime = DATEADD(ss, datepart(ss,runduration), EndDateTime)
update JobAnalysis
set EndDateTime = DATEADD(hh, datepart(hh,runduration), EndDateTime)
-------------------------------
-- Now Insert non-SQL backup data (eg. Veritas), which already has Start & EndDate
insert into JobAnalysis
SELECT ('Backup' + ' '+ a.database_name), '','','',a.backup_start_date/*as StartDateTime*/,'','', a.backup_finish_date /*as EndDateTime*/
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' AND a.backup_start_date >= dateadd(day, -1, GETDATE())
and a.user_name <> 'TAUNTON\SVC_SQL' -- Omit SQL Agent backups with specific user
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.backup_start_date desc, a.server_name, a.database_name
---------------------
-- Now, Find overlapping jobs
Select a.JobName as 'Job 1 Name',
a.StartDateTime as 'Job 1 Start',
DATENAME(weekday, a.StartDateTime) AS 'Day' ,
a.EndDateTime as 'Job 1 End',
DATENAME(weekday, a.EndDateTime) AS 'Day' ,
b.JobName as 'Job 2 Name',
b.StartDateTime as 'Job 2 Start',
DATENAME(weekday, b.StartDateTime) AS 'Day' ,
b.EndDateTime as 'Job 2 End',
DATENAME(weekday, b.EndDateTime) AS 'Day'
from JobAnalysis a
join JobAnalysis b on a.jobname <> b.jobname
where a.StartDateTime >= b.StartDateTime and a.StartDateTime <= b.EndDateTime
-- omit trans log backups, because they run so frequently, they overlap everything.
and a.JobName not in ('Backup - AdvDbPrd Daily Trans Log Backup', 'Backup - Lawson PROD Daily Transaction Log')
and b.JobName not in ('Backup - AdvDbPrd Daily Trans Log Backup', 'Backup - Lawson PROD Daily Transaction Log')
Now I just have to figure out how to display it in a nice GUI !!
August 10, 2005 at 7:14 am
Thank you HomeBrew01.
I have been needing something like that for awhile but haven't had the time to put it together.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply