ReportServer_log.ldf log file size growth

ReportServer_log.ldf log file size growth: The primary reason for the increase in the size of the report server database is the accumulation of high transaction logs, which occur daily as certain users interact with the report. Therefore, the initial step should be to examine the Power BI Report Server (PBIRS) log files. We will see all the steps which will find the resolution for us.

Client Complain:

We have Implemented Power BI Report Server at a client location and there’s a concern from them regarding the ReportServer_log.ldf log file size growth. When the database admin tried to delete or shrink, the report server crashed. We would like Microsoft approach in solving this problem using best practice methods.

TSG 1?

ReportServer_log.ldf

Please go to this log file’s location of PBIRS and check the logs which says “ReportingServerService_timestamp.log” and check the below outcome follows from step 1 to find ReportServer_log.ldf size growth.

Step 1: There could be primary cause for increase in the size of the report server database logs, is the accumulation of high transaction logs, which occur daily as certain users interact with the report. Therefore, the initial step should be to examine the Power BI Report Server (PBIRS) log files.

Note that a cleanup process is scheduled daily at 2 AM system time. Upon reviewing the logs, you will observe one of two outcomes: either the process was successful, or it was not. To confirm you can see these below logs traces:

Failure Traces in log files:

dbcleanup!WindowsService_1!1578!04/19/2024-01:59:59:: i INFO: Expiring old execution log entries
library!WindowsService_1!1578!04/19/2024-02:07:04:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.;
dbcleanup!WindowsService_1!1578!04/19/2024-02:07:04:: e ERROR: Expiration of old execution log entries failed: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out

Success Traces in log files:

dbcleanup!WindowsService_0!19b0!05/21/2024-01:59:59:: i INFO: Expiring old execution log entries
dbcleanup!WindowsService_0!19b0!05/21/2024-02:00:26:: i INFO: Expiration of old execution log entries is complete. Removed 170612 entries.
dbcleanup!WindowsService_0!19b0!05/21/2024-02:00:26:: i INFO: Cleaned 0 broken snapshots, 0 chunks, there were 0 unsuccessful attempts
dbcleanup!WindowsService_0!19b0!05/21/2024-02:00:26:: i INFO: Cleaned 0 parameters values
dbcleanup!WindowsService_0!19b0!05/21/2024-02:00:39:: i INFO: Cleaned 1554 unused segment mappings.

Step 2: If you find that the clean success is not indicated, please proceed to the “executionlogs3” content, and review the entire history from the most recent entry to the earliest by executing this below query on the report server database. If you possess records that date back more than 60 days from today, this could be the main reason for the significant increase in the ReportServer_log.ldf database size.

A) Default log file clean should happens IN 60 days.
>>SELECT * FROM ConfigurationInfo WHERE Name = 'ExecutionLogDaysKept’

B) But if it looks like this is obviously not happening since the data is still growing from beginning then you have run this below query to check the data history present.
>>select count(*), min(timestart) as “startdate”, max(timestart) as “enddate” from ExecutionLogStorage

When you notice an increase in the number of rows or data with start and end range, we need to manually delete the data from “executionlogs3” that is older than 60 days, which could potentially be an issue.

TSG 2?

I have seen couple of cases earlier and found out that the issue might be caused by using full recovery model. Please change the recovery model to simple and test the behavior. The log file “ReportServer_log.ldf” size increase is an expected behavior if you are using subscriptions.

If you still want to use the full recovery model you need to back up the file based on your requirement. Please find below screenshot for changing the full recovery model to simple. For more click here!

TSG’s

1. RDCMan Scaling Is Blurry: Fix In 3 Steps (kingfishertechtips.in)
2. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
3. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
4. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
5. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
6. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
7. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
8. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
9. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
10. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
11. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
12. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
13. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
14. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
15. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
16. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)