July 30, 2012 at 8:57 am
I have a Report Services instance that creates hundreds of jobs. The jobs are in serial format (ie. xxxxxx-xxx-xxxxx-xxxx-xxxx) and clutter up the jobs section view in SSMS. Is there any way to hide these jobs?
July 30, 2012 at 9:06 am
Not that I know of. This is why I keep a separate instance specifically for report server databases on the reporting server. No user databases on it.
Jared
CE - Microsoft
July 30, 2012 at 1:17 pm
I think you can filter the job listing by name... that's a bit of a pain though if you have hundreds of them.
The Activity Monitor will let you filter by category, which is a little easier, but not very useful I'm afraid.
You could create your own view with the job tables in msdb, filtered by category or however you can distinguish the reporting jobs.
Here's a query I created to show jobs with their next scheduled run date/time:
SELECT
Job.Name,
Job.Description,
CASE Job.Enabled WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE '?' END AS Enabled,
CONVERT
(
DateTime,
Stuff(Stuff(LTrim(Str(Schedule.Next_Run_Date)), 7, 0, '-'), 5, 0, '-') + ' ' +
Stuff(Stuff(REPLICATE('0', 6 - Len(LTRIM(STR(Schedule.Next_Run_Time)))) + LTRIM(STR(Schedule.Next_Run_Time)),5, 0, ':'), 3, 0, ':')
) AS Next_Run_DateTime,
Convert(VarChar,Convert(DateTime, Stuff(Stuff(LTrim(Str(Schedule.Next_Run_Date)), 7, 0, '-'), 5, 0, '-')), 110) AS Next_Run_Date,
CONVERT
(
VarChar,
CONVERT
(
DateTime,
Stuff(Stuff(REPLICATE('0', 6 - Len(LTRIM(STR(Schedule.Next_Run_Time)))) + LTRIM(STR(Schedule.Next_Run_Time)),5, 0, ':'), 3, 0, ':')
),
114
) AS Next_Run_Time,
'EXEC msdb..sp_Update_Job @Job_Name = ''' + Replace(job.name,'''','''''') + ''' , @Enabled = ' + CASE Job.Enabled WHEN 0 THEN '1' ELSE '0' END AS [Disable/Enable Query]
FROM
MSDB..sysJobs Job
Join
MSDB..SysJobSchedules Schedule
ON
Job.Job_ID = Schedule.Job_ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply