Restoring SRS (SQL Reporting Services) : Transaction (Process ID xx) was deadlocked on lock resources with another process…

Even though the reportingcomponent of SCOM is a robust solution, sometimes it gets broken. Then a repair is needed in order to make it work again. Many times the underlying SRS component is not OK anymore. There are multiple ways to go about it, but sometimes SRS needs a reset before other correcting actions will work.

The installationmedia for SCOM has a tool for which brings SRS back to it’s state before SCOM Reporting was installed, ResetSRS.exe, located in the folder ~\SupportTools\<systemarchitecture>\.

Just running this tool is not sufficient. Afterwards other steps must to be taken as well, like running the installation of SCOM Reporting again (make sure to connect to the ‘old’ Data Warehouse database !).

However, running this tool might reveal other issues at hand as well. For instance this message might show up when running this tool:

First check the database (ReportServer) on the SQL-server hosting this database. Big change this database has entered Single User mode:

But just running this query ‘ALTER DATABASE ReportServer SET MULTI_USER’ to make the database Multi-User again will not work. Before running it make sure to stop the related SRS service. Now this database will accept this query.

Now run the ResetSRS.exe tool. When all is well this output will be generated:

Now open IE on the SRS server and type in this url: ‘http://localhost/reports’. When all is well this will be shown (it can take a while before it is loaded since the Application Pool has been restarted):

Now the basis (SRS) for SCOM Reporting is Up & Running again and the other actions to make SCOM Reporting work again can be taken.