Category Archives: Business Intellegence (SSIS,SSRS,SSAS)

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.

SSRS_CONFIG_Error

SSRS_URL_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.

Advertisements

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

ssrs_conn_11

SSRS_Conn_2

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

SSRS_LOG1

SSRSLOG

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.
Resolution:

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

http://connect.microsoft.com/SQLServer/feedback/details/519612/reporting-services-2008-crashing

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.

AppPoolNew

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

AppPool1

There are two ways to disable the idle timeout:

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

 AppPool_fixed

 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
‹RecycleTime›1440‹/RecycleTime›

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 @ExecCmd VARCHAR(255)

     
DECLARE @y INT

     
DECLARE @x INT

     
DECLARE @FileContents
VARCHAR(MAX)

     
DECLARE @xmlHandle
INT

     
DECLARE @FILENAME
NVARCHAR(1000)

           
CREATE TABLE #configXML

                 
(

                   
PK INT NOT NULL

                              
IDENTITY(1, 1) ,

                   
[XMLValue] VARCHAR(MAX)

                 
)

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

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

                 
SET @FileContents
= ''

                       
INSERT  INTO
#configXML

                                         
EXEC MASTER.dbo.xp_cmdshell @ExecCmd

                                   
SELECT  @y
= COUNT(*)

                                  
FROM    #configXML

                 
SET @x = 0

                 
WHILE @x <> @y - 1

                                              
BEGIN

                       
                              SET @x = @x + 1

                                                     
SELECT  @FileContents
= @FileContents
+ [XMLValue]

                                                     
FROM    #configXML

                                                     
WHERE   PK
= @x

                                               
END

     
-- Display the file contents

           
SELECT  @FileContents
AS FileContents

           
DROP TABLE #configXML

    
-- Parsing the config file XML

     
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents

    
SELECT  *

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

     (

     
IsSchedulingService VARCHAR(255) 'IsSchedulingService',

     
IsNotificationService VARCHAR(255) 'IsNotificationService',

     
PollingInterval VARCHAR(255) 'PollingInterval',

     
WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage',

      
  RECYCLETIME      
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.

     
SELECT  *

     
FROM    OPENXML
(@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)

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

       --This will
provide extension informaiton

 

              SELECT
localname,text

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

              where
localname='#text'

 

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]
FROM
Catalog
INNER JOIN ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
WHERE
ExecutionLog.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-03-28 23:59:00.000’
) AS RE
GROUP BY
Name
ORDER BY
COUNT(Name) DESC,
Name