Tag Archives: SSRS

Reporting Services not starting, the service failed or the service did not start in a timely fashion.

This was a newly installed SQL Server and after the restart of the server the reporting services failed to start every time when we try to start it. We checked all the areas and found everything fine.

Then we came to know that this is a kind of generic error; the Microsoft Windows Service Control Manager controls the state (i.e., started, stopped, paused, etc.) of all installed Windows services and  by default, the Service Control Manager will wait 30,000 milliseconds (30 seconds) for a service to respond but certain configurations, technical restrictions, or performance issues may result in the service taking longer than 30 seconds to start.


Resolution: By editing or creating the ServicesPipeTimeout DWORD value, the Service Control Manager Timeout period can be overridden, thereby giving the service more time to start up and report ready to the Service.

  1. Click Start, click Run, type regedit, and then click OK.
  2. Locate and then click the following registry subkey:


  1. In the right pane, locate the ServicesPipeTimeout entry.
    Note If the ServicesPipeTimeout entry does not exist, you must create it.

To do this, follow these steps:

On the Edit menu, point to New, and then click DWORD Value.

4. Type ServicesPipeTimeout, and then press ENTER.

5. Right-click ServicesPipeTimeout, and then click Modify.

6. Click Decimal, type 60000\120000, and then click OK.


This value represents the time in milliseconds before a service times out.


SSRS 2008 Problem : Message: Invalid URI: The Authority/Host could not be parsed

You could face this issue while connecting to Reporting Services from configuration manager.

The below error mainly appears while accessing Web Service URL property in Reporting Services configuration manager and the main reason behind is a broken\invalid <UrlString> tag.



To confirm this go to location mentioned below and open rsreportserver.XML file in notepad. \Program Files\Microsoft SQL Server\MSRS10.SQL2008_PROD\Reporting Services\ReportServer

Resolution: Correct the <UrlString> </URLString> accordingly and save the file, and it should work now.

Report server has detected a possible denial of service attack

We encountered a strange issue where the reporting services stops responding for some user. When we investigate the issue and check event viewer we found below mentioned warning

Warning: The report server has detected a possible denial of service attack. The report server is dropping requests for service from the xxx.xxx.xxx.xx

Warning 2: Exception information:
      Exception type: HttpException      
                  Exception message: Server Too Busy



The reason could be here that the number of connections from the same user exceeds the maximum allowed number of concurrent connections from one user, the reporting services will not handle new requests and it will wait until existing requests have terminated.

By default Reporting Services can only allow 20 max connections from one user as shown below hence to resolve this issue you need to increase this value as per your requirement.

Resolution: To resolve this issue go below mentioned location or the location where reportserver config file is available as per your installation.

\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer

  • Open the file in notepad and find “MaxActiveReqForOneUser” and change its valle as per your need like from 20 to 50.

<Add Key=”MaxActiveReqForOneUser” Value=”20″/>

Hope this will resolve your issue.

SSRS ERROR: Managed server timed out. Timeout=150 sec. GC extension timeout=30 sec.

We had an issue in which the reporting services 2008 running on Windows 2008 suddenly stops responding and when we connect to reporting services it’s not responding, even the configuration manager fails to load Reporting Services Configuration in RS configuration Manager.

We explore the Reporting Services logs which generally available on below location and we found something interesting.

ERROR: Managed server timed out. Timeout=150 sec. GC extension timeout=30 sec.

%ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles



During investigation it seems like a bug in Reporting Services; investigation reveals that the major cause for reporting services to be  unresponsive\crash was slow GC (Garbage collection- it manages the allocation and release of memory for your application). This error can be seen clearly in logs “Granting additional timeout of 30 sec.” indicates that managed server timed out while GC was in process, and so slow GC was the likely reason.

Slow GC occurs usually when you have a lot of RAM on the box and the report server process is using a lot of it. The problem is that for managed applications, a Garbage Collection (GC) occurs periodically. During a GC the process is suspended temporarily.

MS suggest that a long running queries\process related to SSRS must be killed from query analyzer should resolve this issue.


Resolution which worked for US

We tried several things but at last the resolution which worked for us is to apply a latest SP3 for SQL 2008, after applying latest SP the reporting service went to its normal consistent state.


Slow response on opening first report and after a certain timeout period or after SSRS restart

The first call to reporting services is very slow if you access report after some long interval or your reporting services have been restarted. This is as per design as when reporting services have been idle for a time, the next hit on it can be extremely slow and this is with every report not any individual and related to IIS. The problem is that when the IIS is not getting any traffic, it “goes to sleep”, to clear up memory for other applications. This is totally understandable and a side-effect of Reporting Services’ architecture. Consider that RS is a managed code application that’s persisted on disk as an executable but in intermediate language which must be compiled to machine-code before it can be run. This compile phase is only repeated when the service has not been used in a while. If left idle, the system will mark the assembly as “discarded” and permit it to be overlaid by other operations. 

There are some solutions to it, depending of your version of IIS and SQL server.

Reporting services 2005 with IIS 6 and above

  • Open IIS manager
  • Under application pools right click DefaultAppPool and select properties.


  • Under the performance tag, you can change the value for the idle timeout. Default is 20 minutes.


There are two ways to disable the idle timeout:

  • Uncheck the checkbox in front of the setting or set it to 0.


 After this change I found that earlier first report took 2-4 minutes to generate, but now it is ready in a few seconds.

Reporting Services 2008\2008R2
In SSRS 2008 and above version there is a XML configuration file with a setting for “Recycle Time”. The recycle time is a scheduled timer in minutes based on when the last time SSRS has been manually restarted by a user. When a recycle occurs idle resources within SSRS are freed. This value can be changed to allow SSRS to keep resources for a longer period to prevent slowdowns due to re-initializing SSRS resources. SSRS is configured to recycle itself every 720 minutes, or 12 hours.
This setting can be changed in report server configuration file rsreportserver.config. This file is located in your SSRS installation directory, for example D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer
As the suitable time for SSRS recycle depends on the usage and you can adjust It as per your need in the configuration file as shown below

Another Workaround
There is one more possible workaround solution to this issue which rests on the scheduler and execution of a PowerShell script, which stops and starts the SSRS service and after the restart it makes a request to the report manager URL which forces the reporting services to load all the configurations. Then all the subsequent request to SSRS are immediate.
Hence every morning you can schedule below script to restart SSRS and load all configuration before the business operations starts.

Stop-Service “SQL Server Reporting Services (MSSQLSERVER)”
Start-Service “SQL Server Reporting Services (MSSQLSERVER)”
$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(“http://localhost_SQL2008_Prod/Reports/Pages/Folder.aspx“)


How to read and parse rsreportserver.config file for Report Server in SSRS





DECLARE @FileContents

DECLARE @xmlHandle





IDENTITY(1, 1) ,



SET @FileName = 'C:\Program Files\Microsoft SQL Server\MSRS11.SQL01\Reporting

SET @ExecCmd = 'type ' + '"' + @FileName + '"'

SET @FileContents
= ''


EXEC MASTER.dbo.xp_cmdshell @ExecCmd

= COUNT(*)

FROM    #configXML

SET @x = 0

WHILE @x <> @y - 1


                              SET @x = @x + 1

SELECT  @FileContents
= @FileContents
+ [XMLValue]

FROM    #configXML

= @x


-- Display the file contents

SELECT  @FileContents
AS FileContents


-- Parsing the config file XML

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents


(@xmlHandle, '//Service', 2) WITH


IsSchedulingService VARCHAR(255) 'IsSchedulingService',

IsNotificationService VARCHAR(255) 'IsNotificationService',

PollingInterval VARCHAR(255) 'PollingInterval',

WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage',

VARCHAR(255)      'RecycleTime'


--This will provide you the value of your 2 parameters for
ReportServer URL and Report Manager URL

--The first value would be Report Server output and the 2nd value
is Remport Manager.


(@xmlHandle, '//URL', 5) WITH


UrlString VARCHAR(255) 'UrlString',

AccountName VARCHAR(255) 'AccountName'


 --This will give you important information
about authenticaion, MaxActiveReqForOneUser, and database timeout


       Select *

      From  OpenXML (@xmlHandle, '//Configuration',0)

ID in (854 ,855,856,857,74,75)

       --This will
provide extension informaiton



OPENXML(@xmlHandle, '//ModelGeneration',0)



EXEC sp_xml_removedocument @xmlHandle



How to get most frequent used top 10 reports on report server

SELECT TOP 10 COUNT(Name) AS ExecutionCount, Name,
SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
SUM(TimeProcessing) AS TimeProcessingSum,
SUM(TimeRendering) AS TimeRenderingSum,
SUM(ByteCount) AS ByteCountSum,
SUM([RowCount]) AS RowCountSum
FROM (SELECT TimeStart, Catalog.Type,Catalog.Name,TimeDataRetrieval, TimeProcessing,TimeRendering,ByteCount,[RowCount]
INNER JOIN ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
ExecutionLog.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-03-28 23:59:00.000’

How to get detailed execution history of reports on Report Server

SELECT  EL.TimeStart , EL.TimeEnd,DATEDIFF(s,EL.TimeStart , EL.TimeEnd) ‘Duration’ ,COALESCE(C.Path, ‘Unknown’) AS ItemPath, EL.UserName,EL.ExecutionId, CASE(EL.RequestType)
  WHEN 0 THEN ‘Interactive’  WHEN 1 THEN ‘Subscription’  WHEN 2 THEN ‘Refresh Cache’  ELSE ‘Unknown’END
   AS RequestType, — SubscriptionId
    EL.Format, Parameters, CASE(EL.ReportAction)   WHEN 1 THEN ‘Render’
    WHEN 2 THEN ‘BookmarkNavigation’  WHEN 3 THEN ‘DocumentMapNavigation’
     WHEN 4 THEN ‘DrillThrough’  WHEN 5 THEN ‘FindString’  WHEN 6 THEN ‘GetDocumentMap’  WHEN 7 THEN ‘Toggle’
      WHEN 8 THEN ‘Sort’  WHEN 9 THEN ‘Execute’  ELSE ‘Unknown’END AS ItemAction,EL.TimeStart, YEAR(EL.TimeStart)
       AS Start_Year,MONTH(EL.TimeStart) AS Start_Month,DATENAME(MONTH,EL.TimeStart) AS Start_Month_Name,
       DATENAME(DW,EL.TimeStart) AS Start_Day_Of_Week,DATEPART(WEEKDAY,EL.TimeStart)
       AS Start_Day_Number_of_Week,EL.TimeEnd, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering,
       CASE(EL.Source)  WHEN 1 THEN ‘Live’  WHEN 2 THEN ‘Cache’  WHEN 3 THEN ‘Snapshot’   WHEN 4 THEN ‘History’
        WHEN 5 THEN ‘AdHoc’  WHEN 6 THEN ‘Session’  WHEN 7 THEN ‘Rdce’  ELSE ‘Unknown’END AS Source,EL.Status,
        C.CreationDate,C.ModifiedDate,CASE   WHEN C.TYPE=1 THEN ‘Folder’  WHEN C.TYPE=2 THEN ‘Report’
        WHEN C.TYPE=3 THEN ‘XML’   WHEN C.TYPE=4 THEN ‘Linked Report’  WHEN C.TYPE=5 THEN ‘Data Source’
         WHEN C.TYPE=6 THEN ‘Model’  WHEN C.TYPE=8 THEN ‘Shared Dataset’  WHEN C.TYPE=9 THEN ‘Report Part’END
         AS Type_Description
          FROM   ExecutionLogStorage AS EL  LEFT OUTER JOIN Catalog AS C ON
          (EL.ReportID = C.ItemID)
          C.Path like ‘%QuickTrend%’
          EL.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-02-28 23:59:00.000’
          ORDER By 1

How to get Snapshot associated with the report

SELECT Reportname = c.Name
,ScheduleType = sch.EventType
,ScheduleFrequency =
CASE sch.RecurrenceType
WHEN 1 THEN ‘Once’
WHEN 2 THEN ‘Hourly’
WHEN 4 THEN ‘Daily/Weekly’
WHEN 5 THEN ‘Monthly’
FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON su.Report_OID = c.ItemID
JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
ON rsc.ReportID = c.ItemID
AND rsc.SubscriptionID = su.SubscriptionID
JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName