SSRS jobs in MSDB

January 8, 2016 at 10:00

When you have Reporting Services (SSRS) running in your environment and have created subscriptions to reports you will notice these very descriptive jobs appearing on your SQL Server with names like 38EE888E-5943-4CCE-9EA9-26240B226BB4 or 9C2C8FAB-83EB-408E-AEA2-BD9C59DA4951 or something else ;). When you open the properties of these jobs you hope to find a good description of the job in the description field. unfortunately this is not the case. The description says:

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.

This is also not to helpful. I wanted to know which of these jobs were in use for which subscriptions and decided to investigate and see if I could find this info.

First solution

Luckily finding the information turned out to be quite simple. The name of the job corresponds to the ScheduleID column in the ReportServer database table Schedule. Joining this table with a few other tables in the ReportServer database and I got the results I wanted. The following query returns the information about which subscriptions and the reports that are processed in job with ID 9C2C8FAB-83EB-408E-AEA2-BD9C59DA4951

This gives me the information I wanted, but I wanted to see this information in the job description. So it was time for the next phase.

Permanent solution

I decided to create a script that creates a useful description an store it in the job description. I also wanted to see on which schedule the job runs (actually it was my colleague who wanted this). This proved to be a little bit more challenging since the MSDB database has a very interesting structure (for instance storing time as an int where 40000 equals 4:00:00 am). With a little tracing I was able to create a good description of the job schedule. and include it in the job description. now it is possible to view information about which subscriptions a job handles and when it executes. The script can be found here. Feel free to modify it to your own needs, but remember to test this script first before running it on you production environment

Tags: