If you’ve been using SQL Server Reporting Services (SSRS) for a while, chances are you have hundreds of published reports available to users. Over time many of these reports may have become obsolete or are no longer used. Identifying these reports from the SSRS front-end can be very challenging, if not impossible. Though there are a number of factors to consider when deciding to retire an SSRS report one of the most common tends to be whether or not the report is still being utilized.
One of the best methods for determining whether a report is still being used is to look at the execution log in the SSRS database. The following script will query both the
CATALOG table and
EXECUTIONLOG table within the SSRS database to return a list of reports that have not be executed for a specific number of days.
USE  --Specify SSRS Database
DECLARE @NumDays INT
SET @NumDays = 365 --Specify Number of Days
FROM dbo.catalog c
INNER JOIN (SELECT ReportID,LastUsedDate= MAX(timestart)
GROUP BY ReportID) e ON c.ItemID = e.ReportID
WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NumDays
ORDER BY DaysNotUsed DESC
The example above will return a list of all reports that have not been used in the past 365 days, though this can be modified to allow for any number of days to be specified.
The screenshot below illustrates an example of the type of output you can expect to see using the above script. As you can see, there are several reports that have not bee executed in over 450 days. The name of the report and the path to each report is provided, making it easy to navigate to each report from the front-end of SSRS.
Once you have identified all reports that have not been run within a particular time frame, the best option is to archive the reports by moving them to a specific folder. Deleting reports, even outdated ones, is never a good idea as there are certainly cases where reports may only be used once every year or two.
By moving the reports to an archive folder you can keep a record of all reports that have been retired as well as quickly restore any reports that were retired in error. Once you have determined where you will be storing any reports that have been retired, you may also want to modify the above script by adding an
AND clause to exclude the retired reports directory.
For example, if you were putting all retired reports into a folder named Retired Reports you could add the following
AND clause to the script:
AND Path NOT LIKE ('%Retired Reports%')
It’s also a good idea to strip the security permissions on all reports and remove any subscriptions once the report has been moved to the archive directory. Security should be configured on the archive directory to prevent users from accessing any report that have been archived.
How you choose to manage retired reports is completely up to you but keeping your reports database organized and free of outdated and unused reports is an easy way to keep SSRS running smoothly.