SQL Server 2008 R2 – How to Remove Old SSRS Schedule Jobs

sql-server-2008-r2ssrs

We have a application that creates SSRS Schedules 2 times a year and it does create one for each of our customers.

Each schedulation results in a SQL Agent job with a GUID as name and the description This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job..

The schedulations just run one time and then just collects dust.

Is there any way to remove all old jobs and information about the schedulations in the SSRS database?

Best Answer

I found a solution to this problem. Create the following files:

RemoveSubscriptions.bat:

@echo OFF
SET Server=http://localhost/ReportServer_MSSQL_2008
SET ReportUrl=/test/reports/testreport
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe" -i RemoveSubscriptions.rss -s %Server% -e Mgmt2010 -v reportUrl=%ReportUrl% > %date%.log

RemoveSubscriptions.rss:

Public Sub Main()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    Dim subscriptions as Subscription() = rs.ListSubscriptions(reportUrl)
    For Each item As Subscription in subscriptions
        'Delete all subscriptions that are older then one month
        If (item.LastExecutedSpecified AndAlso item.LastExecuted.AddMonths(1) < Date.Now) Then
            Console.WriteLine("Removing subscription with id {0} from {1}", item.SubscriptionID, reportUrl)
            rs.DeleteSubscription(item.SubscriptionID)
            Console.WriteLine("{0}, {1}, {2}, {3}", item.SubscriptionID, item.Active, item.LastExecuted, item.LastExecutedSpecified)
        End If
    Next
End Sub

Now if you change the variables in the bat file and run the bat file it should remove all subscriptions that are older then 1 month this includes the actual sql job.

Warning: This will remove jobs that run more then once so if you have a report scheduled to run once a year it will be lost!

Related Question