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.

SSRSNotstarting

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:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

  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.

ServiceNamedPipe

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.

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.

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.

 

Unable to add new Article in Replication

I encounter an interesting issue on Transaction Replication where I was unable to add a newly added table into some subscriber. My replication setup consists of 1 Publisher and 7 subscribers.

I followed the below step to add a new article in my existing Publication which is working fine in my Development environment which is almost similar to Production.

  1. To avoid complete snapshot of the Publisher database I set allow_anonymous and Immediate_sync to False by default its set to TrueEXEC sp_changepublication @publication = ‘PubDB’,
    @property = ‘allow_anonymous’,
    @value = ‘false’
    GO
    EXEC sp_changepublication @publication = ‘PubDB’,
    @property = ‘immediate_sync’,
    @value = ‘false’
    GO
  2. Added new table into exiting publication properties
  3. Start Snapshot Agent
  4. Snapshot Agent created a Snapshot for one article and the scripts can be seen in repl data folderSnapshot_generation
  5. Started Distribution Agent (Ignore if its already running) to deliver this snapshot on the subscriber and it should look like this as shown belowSnapshotDelivered1

Now here I noticed that on 3 of the sites out of 7 Distribution Agent did not apply snapshot on the subscriber and after some time I noticed that the folder gets deleted for this snapshot containing schema, index ,BCP file etc.

This is because my distribution agent cleanup job was configured to run after every 5 min to control the size of distribution database and shared drive containing snapshots.

EXEC dbo.sp_MSdistribution_cleanup  @min_distretention = 0, @max_distretention = 72

Initially I focused on one of the properties immediate sync = TRUE, which I believe could be responsible for deleting the snapshot before applying it on the subscriber.

But after looking into my distribution agent cleanup job properties and after referring to Article my assumption start getting wrong here the difference is that in my case its set to @min_distretention = 0 so do not wait and delete the transaction once it delivered to subscriber case

If “immediate sync” = TRUE, do not keep transaction EVEN IF they have been replicated to the subscriber but delete any transaction older the 72 hours.  Metadata that is younger than 72 hours, but older than 4 hours will be kept. This is conservative.

If “immediate sync” = FALSE, then keep transaction for at least 4 hours but if the transaction has already been replicated (distributed) to the subscriber go ahead and delete EVEN IF not older then 72 hours. Metadata that is younger than 72 hours, but older than 4 hours may be deleted quite aggressively.

Later on I focused on Replication Script and noticed that there is a small change in the Subscription properties of these 3 subscribers as shown below but the impact is big.

@sync_type = N’replication support only’

subprop

This property allows you to manually synchronize your data and schema on your publisher and your subscriber, but then the support objects needed to make replication work are pushed automatically to the subscriber. The benefit is that you can still take advantage of the replication tools to setup replication, but you also have more control over getting your data and schema in sync.  This is great if you already have a database in place and you only want to setup replication. Instead of having to drop and recreate the tables, this option will just push out the objects needed for replication and not affect your existing schema or data.

But you will not be able to add new article in publication once you configure subscriber with this option and this is by design “Replication Support Only” 

Property

Work Around:-

There is no direct way to fix this as this property and this property is not available in GUI and can not be modified, so you have to drop and recreate the subscription with sync_type = N’automatic’. But in a mission critical application this is not possible so one of the approach is to manually apply the script on subscriber. If you have the script copied from the repl data folder then execute that script on all the subscriber which are having this problem.

legal

Important:- While you are manually adding this article ensure that no transaction are made into this table else you will encounter errors like “Object Note Found” or “Row Not Found”.

Resolution:-

  • Create a new database with same name on another SQL instance and make changes in web.config to point to this database, meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’
  • If your application is accessing subscriber database using Synonyms from other database then create a database with same name (using restoration)on same SQL instance and point synonym to this database and meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’

When this option was enabled on my subscription and why?

Well there is no direct way to identify when this configuration was done so you have to rely on distribution agent creation job date or check for subscriber database restoration date. This option might give you some idea if  in past the replication was synchronized with this option to avoid any down time.