SSRS 2019 & Powershell Reporting Tools Getting Deadlocks When Uploading

Question

Hi,

Please may you assist.

We have recently migrated to SQL 2019 and utilising SSRS 2019 now.

We have a custom task in Azure Devops that was created using Powershell ReportingServicesTools

The task removes the current SSRS Reporting site and then uploads all the new reports again.

We may have up to 5+ reporting sites on 1 database server running SSRS.

When 2 or more of the sites deploy at the same time I'm getting the following error on [only] one of the sites requiring a new deployment

[error]Failed to create item {ReportName}.rdl : Failed to create catalog item {UploadPathtoReport}.rdl : Exception calling "CreateCatalogItem" with "7" argument(s): "System.Web.Services.Protocols.SoapException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> 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: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

We never got this using SSRS 2016 and SQL 2016 (same server we upgraded).

The error generally occurs either on the step "Remove-RsCatalogItem" or mostly "Write-RsCatalogItem"

It uses a local admin account on the server with access to the report server as a proxy

Is there any way to resolve this?

Thank you.

Answer 1

Hi @Trevor Fourie ,

Based on the error you provided. The issue should be related to the report server database.

If this is a timeout issue, By default, the default number of seconds after which a connection to the report server database times out is 120. While rendering a report, the reporting services need to store the exection information back to the report server database.

You could refer to this thread to deal with it:
https://social.technet.microsoft.com/Forums/es-ES/db4ca6c2-5445-4ff9-9f63-e20f3859cc70/error-throwing-microsoftreportingservicesdiagnosticsutilitiesreportserverstorageexception-an

If it is not timeout related, it might be that when migration, the sql server agent job related to SSRS has not been migrated successfully which leads the report server creates logs consistently.

It will be required to recreate the database in this case.
See ssrs-2016-logging-same-message-250-times-per-second & an-error-occurred-within-the-report-server-database-this-may-be-due-to-a-connection-failure

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

Good Morning Zoehui,

Thank you for the information. Based on the info that you provided, I can confirm that we did not migrate the report server to SQL 2019, but rather did a fresh installation and redeployment of our reports.

The only resolution that I can see with your post, would then to be to recreate the Reportserver databases, and redeploy the reports again? I would like to avoid this solution (if possible) as I think we don't understand the root cause, and it will likely occur again.

We have set the timeout limit on SSRS to 1200, but the error still persists.

Is there a way to avoid the deadlocks when working with "Remove-RsCatalogItem" and "Write-RsCatalogItem" in powershell?

We have some errors, however not sure if they are related to this issue in the ssrs log file:

ERROR: Exception caught instantiating TERADATA report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Teradata.Client.Provider, Version=12.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c' or one of its dependencies. The system cannot find the file specified.

Exception caught instantiating ORACLE report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.

ERROR: Exception caught instantiating SQLPDW report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Unable to load assembly Microsoft.ReportingServices.Cloud.DataExtensions, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ReportingServices.Cloud.DataExtensions' or one of its dependencies. The system cannot find the file specified.

ERROR: Exception caught instantiating DAX report server extension: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ReportingServices.Cloud.DataExtensions' or one of its dependencies. The system cannot find the file specified..

Your help is appreciated.

G