Reporting Server down unexpectedly
We have a server which has Sql Server 2005 and the reporting server running on it. Now it happens sometimes that the reporting server stops working. We get the following errors randomly whenever we try generating a report
1) Report item not found
2) Transaction error - this is a long error, which has some transaction number and some other stuff
We looked the application Event viewer for the exact problem. It said the "Report Server (MSSQLSERVER) cannot connect to the report server database".
We then opened the reporting Service configuration tool and under the database setup, connected to the database again and applied changes so as to grant rights to the DB again.
After this the reporting server worked fine and reorts were getting generated.
This problem comes has come a number of times already and we can't be expected to do the above fix everytime.
If anybody knows why this issue comes and how it can be resolved please let me know