If you’re using Jet Hub with Jet Reports, then when you run a report, the report run is stored in the Jet Services database;through time, this report run history can grow quite large if you either have large reports or are generating lots of reports; combine these together and the history can grow to potentially massive sizes.
Jet Hub does not, unfortunately, include an automated clear-down routine for the report run history, but, fortunately, the history is only stored in a single table.
The below stored procedure can be created against the Jet Services database and scheduled to run with SQL Server Agent; the highlighted parameter at the top can be changed to alter the number of months for which history should be kept:
IF OBJECT_ID (N'usp_AZRCRV_DeleteJetReportsReportRuns', N'P') IS NOT NULL DROP PROCEDURE usp_AZRCRV_DeleteJetReportsReportRuns GO CREATE PROCEDURE dbo.usp_AZRCRV_DeleteJetReportsReportRuns @iAge INTEGER = 12 AS /*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ DELETE FROM ReportRuns WHERE Runtime < DATEADD(month, -@iAge, GETDATE()) GO
As always, test the script before using against a live system and ensure you have a good backup before