February 22, 2012 at 7:19 am
Does anyone know the right way to do this? I have been all over the web and these forums and I have been unable to find any definitive source on how to do this. I had the code (below) for SQL 2005 RS setup as a scheduled job but it is only part of the clean up. I am hesitant to start clearing out from Subscriptions, Schedule, Report Schedule tables as the few articles I found in the past said to be extremely careful, if the wrong data is removed it will cause all kinds of issues. I have a self service interface that will create subscriptions but then the job agent gets filled with the GUID ID's and we are manually going into each report manage and removing them. Thanks for your assistance.
select j.name, j.job_id
into #oldSubscriptions
from [msdb].[dbo].[sysjobs] j
left join [ReportServer].[dbo].[Schedule] r on (r.scheduleid = j.name)
left join [msdb].[dbo].[sysjobschedules] s on (s.job_id = j.job_id)
where j.description like '%owned by a report server process%'
and next_run_date < YEAR(CURRENT_TIMESTAMP)*10000 + MONTH(CURRENT_TIMESTAMP)*100 + DAY(CURRENT_TIMESTAMP)
declare @jname varchar(250)
set @jname = (select top 1 name from #oldSubscriptions)
while @jname is not null
begin
EXEC [msdb].[dbo].sp_purge_jobhistory @job_name = @jname
delete #oldSubscriptions where name = @jname
set @jname = (select top 1 name from #oldSubscriptions)
end
-- clean up
drop table #oldSubscriptions
February 22, 2012 at 11:41 am
bump
February 23, 2012 at 3:34 am
bump
February 24, 2012 at 11:21 am
WOW - 89 views and no responses! Good to know my lack of finding information on this topic is not due to poor reasearch skills;-)
I imagine there are others running into this issue and must be manually removing the old jobs...Buller?
February 26, 2012 at 8:54 pm
...Bueller?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply