Wednesday, 19 September 2012

SQL 2008 R2 Reporting Services won't restart after SP2

Seems a few people have encountered a problem with SSRS 2008 R2 after installing SP2.

In my case the SSRS instance would not restart, so I checked Event Viewer.
There were no messages at all under the Application log, and all the system log stated was:

Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (Grumpy) service to connect.

I tried changing the account that SSRS was running under, but it would still not restart, and I was considering un-installing SP2 (KB 948109).

A bit more investigating though, and there didn't seem to actually be any error from SSRS, so I tried changing the service startup timeout value to 60 seconds.

This time SSRS started successfully (albeit slowly).  It did require me to backup the encryption key file, but I think that is more likely to have been related to playing around with the account it was running under rather than the timeout change.

The post I found that details changing the timeout is as follows:

  • To increase the timeout value in the registry, follow these steps:
    1. Start Registry Editor (Regedit.exe).
    2. To change the value data for the ServicesPipeTimeout DWORD value to 60000 in the Control key, follow these steps:
      1. Locate and then click the following registry key:
        HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
      2. Click the Control subkey
      3. Right-click the ServicesPipeTimeout DWORD value, and then click Modify.
      4. Click Decimal.
      5. Type 60000, and then click OK.
    3. If the ServicesPipeTimeout value is not available, add the new DWORD value, and then set its value data to 60000 in the Control key. To do so, follow these steps:
      1. Locate and then click the following registry key:
        HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
      2. Click the Control subkey.
      3. On the Edit menu, point to New, and then click DWORD Value.
      4. Type ServicesPipeTimeout, and then press ENTER.
      5. Right-click the ServicesPipeTimeout DWORD value, and then click Modify.
      6. Click Decimal.
      7. Type a value of 60000, and then click OK.

        The value is 60000 milliseconds and is equivalent to 60 seconds or to one minute.
      Note This change does not take effect until the computer is restarted

15 comments:

Justin said...

Thank you GrumpyDBA. I had exactly the same problem as you, once I created the key and rebooted, the service came online. Great stuff.

Anonymous said...

Well done ! It works !

Excellent info !

Anonymous said...

Great post.
Often on VM the service takes longer than 30 seconds to start.

Anonymous said...

Just hit this issue this morning and this fix worked for me too - thanks!

Anonymous said...

Worked like a charm!! Thanks!

Anonymous said...

Thanks! The fix worked for me.

Chris Golla said...

Worked like a charm!!!!

sqlservermasters said...

Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
http://sqlservermasters.com/

Anonymous said...

Thanks Grumpy. You're a star!

Anonymous said...

Yep - this minimised our 'panic time' when we hit this problem. Thanks for taking the time to share it.

Anonymous said...

doesn't work for us, but this worked fine :
try disabled the certificate checking in the ReportingServicesService.exe.config file:

Edit the ReportingServicesService.exe.config file usually located in this folder: C:\Program Files\Microsoft SQL Server\\Reporting Services\ReportServer\bin
Add the "" within the tag
Patch the server with the KB.

Anonymous said...

Add the "<"generatePublisherEvidence enabled="false""/>" within the "<"Runtime">" tag

Anonymous said...

Cheers Mr Grumpy DBA, I've installed SP2 on 50+ servers since it came out and never had an issue. Tonight 10pm and I'm installing it on a new server a software company setup while I was away on leave and Reporting Services doesn't start ... great and on a 24/7 system too.

But you've saved the day and everything is back up and running.

The issue appears to be the VM just wasn't getting the Service started in the default time limit. I'm guessing the VM Host it was on was overloaded at the time causing it to run slowly but I'll check on that tomorrow morning.

Srini said...

Similar post helped me. Thanks.
http://sqldbworld.blogspot.com/2015/11/sql-server-reporting-services-fails-to.html

Unknown said...

Still useful nearly 4 years on! Just inherited some out of date 2008 R2 instances and hit this issue for the first time.

Post a Comment