topic Re: Increase the number of records kept per Subscription in dbo.SubscriptionHistory in Report Server

Increase the number of records kept per Subscription in dbo.SubscriptionHistory

AustinS — Mon, 28 Jun 2021 14:38:04 GMT

I am wondering if there is anyway to configure the number of history records per subscription kept in dbo.SubscriptionHistory. From my testing, I can only see the previous 10 subscription history records.

Example: If I have a scheduled Power BI report refresh set to refresh every hour, I cannot see if the refresh failed yesterday or what the error messages might have been as I only have the previous 10 hours of refreshes for that subscription.

Let me know if there is any configuration option or how you keep a longer refresh history.

Re: Increase the number of records kept per Subscription in dbo.SubscriptionHistory

HashamNiaz — Mon, 28 Jun 2021 18:16:53 GMT

Hi AustinS !

You can increase the Retention days for Execution logs by setting [ExecutionLogDaysKept] propterty to [-1];

https://docs.microsoft.com/en-us/sql/reporting-services/tools/server-properties-advanced-page-reporting-services?view=sql-server-ver15

https://jackworthen.com/2020/07/06/changing-the-report-execution-log-retention-policy-in-sql-server-reporting-services/

This will keep Report Execution Logs but it seems there is no setting to increase subscription retention period.

Regards,

Hasham

Re: Increase the number of records kept per Subscription in dbo.SubscriptionHistory

AustinS — Mon, 28 Jun 2021 20:55:32 GMT

Upon further evaluation, it is pretty clear that it is hard coded to be 10 records within the stored procedure dbo.AddSubscriptionHistoryEntry.

Code snippet from dbo.AddSubscriptionHistoryEntry

DELETE FROM [dbo].[SubscriptionHistory] WHERE [SubscriptionID] = @SubscriptionID AND [SubscriptionHistoryID] NOT IN ( SELECT TOP (10) [SubscriptionHistoryID] FROM [dbo].[SubscriptionHistory] WHERE [SubscriptionID] = @SubscriptionID ORDER BY [StartTime] DESC )

I am pretty hesistant to make any changes to the stored procedures of the Power BI Report Server.

Re: Increase the number of records kept per Subscription in dbo.SubscriptionHistory

HashamNiaz — Mon, 28 Jun 2021 21:07:15 GMT

Hi AustinS !

You can make the change in stored procedure dbo.AddSubscriptionHistoryEntry to only let it delete entries which are older than 60 days.

The thing to remmeber is when you upgrade to newer version from Jan 2021 to May 2021 or may be more latest, you will always need to make sure these maintainence sp's preserve your customize code.

Glad you find out the solution.

Regards,

Hasham