I am faced with a recurring issue of reporting service not starting up after the weekly scheduled reboot of SQL server 2008 R2 production server , even though the service is set to automatic. As per Microsoft, their suggestion is to apply the CU 7 ( KB 2844090) to fix the issue, but then we were not facing the same issue with QA server . I have spent some more time by going through the reporting service log file. The issue has now been resolved without applying the patch. I have changed the service parameter at registry and rebooted the server to ensure that SSRS is turned on after the reboot. The SSRS services started automatically after the reboot and believe we would not encounter this issue from next week onwards.
Issue: SSRS services did not start after the weekly reboot
•Open Reg Edit
•Navigate to the following registry key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control”
•Right-click Control, and create a new DWORD with the name “ServicesPipeTimeout”, and then press ENTER.
•Right-click “ServicesPipeTimeout”, and then click Modify.
•Select Decimal, and type 60000 (this will allow the service 1 minute to start)
Let’s take a look at sys.dm_os_ring_buffers to understand the scenario .
Running this query against the system:
SELECT * FROM sys.dm_os_ring_buffers AS dorb;
There are only four columns, but the final column is XML and that’s where the good information can be found. There are a number of different ring buffers but we’re only interested in the type called RING_BUFFER_RESOURCE_MONITOR. This records changes to memory allocations, which is a great way to see when you’re running out of memory because a message that your memory is low is a pretty good indicator that you might be seeing memory pressure.
The information presented in the MemoryNode and the MemoryRecord are useful for attempting to figure out what went wrong, but the key points are up in the ResourceMonitor element with the Notification, IndicatorsProcess, and IndicatorsSystem values. First, the notification tells us that this was RESOURCE_MEMPHYSICAL_LOW message that was captured in the ring buffers. That means that physical memory was low. The next two indicators let us know what was low. If the IndicatorsProcess returns 0 and the IndicatorsSystem returns a value then the problem was system wide. But, in our case the IndicatorsProcess has a value and IndicatorsSystem is returning 0. This means that this alert was for a single process that ran suffered from low memory, not the entire system. The values break down as follows:
Value Meaning 1 High Physical Memory 2 Low Physical Memory 4 Low Virtual Memory