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

[AustinS]

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. 

[AustinS · Accepted solution]

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.

[HashamNiaz]

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

[HashamNiaz]

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-report... 

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

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

Regards,

Hasham