Jul 312011
 

As I wrote in my previous blog post First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services, a first report after specific time takes very long to start.

The issue is caused by the way how SSRS works and SSRS regularly restarts application domain after specific time period. After the application domain is restarted, then upon first request to the SSRS it needs to load all the settings and it takes quite a long time.

There is no real solving to the issue except increasing the interval between the application domain restarts from default 720 minutes to other value which meets your business needs more closer.

However even after increasing the value, then after the period is reached, the application domain is restarted and again the first request will take a long time. It could be ideal to optimize the interval so the app domain restart is done out of business hours. however even then fist report will take a long time.

Here is a possible workaround solution. It rests on the scheduler and execution of a PowerShell script, which stops and starts the SSRS service (which has the same effect as the application domain restart) and after the restart it makes a request to the report manager URL which forces the reporting services to load all the configurations etc. Then all the subsequent request to SSRS are immediate.

So if we set the RecycleTime in the rsreportserver.config to a value which is over one day let’s say 1500 minutes (it is 25 hours) and schedule the execution of the PowerShell script out of the business hours, each morning we will have SSRS ready without any delays. For details about modifying the RecycleTime take a look on my previous post mentioned above.

So here is the PowerShell script:

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/Reports/Pages/Folder.aspx")

The script above first stops the SQL Server Reporting Service of the default (MSSQLSERVER) instance and immediately starts it again (stopping and starting the service has the same effect as application domain recycling). Then an webClient object is created which is used to fetch the Report Manager page which causes the reporting services to load all the settings. The page is read as string (it doesn’t matter how we read the page. Important is to make a request to initialize the reporting services) and it will take a longer time (like the first report start).

It is also important to get the DefaultNetworkCredentials of the user account under which the script will be executed. It is necessary to assign those credentials to the web client so it can authenticate to the reporting services.

Also it is important to mention that it is necessary to execute the script with elevated administrative privileges to be able to stop and start the service.

You can create a scheduled task using the Scheduled Tasks GUI or execute a below command to create the scheduled task from within a command prompt. The command prompt needs to be running with elevated administrative privileges.

schtasks /create /tn "SSRS Recycle" /ru UserName /rl highest /np /sc daily /sd 08/01/2011 /st 02:00 /tr "powershell.exe -noprofile -executionpolicy RemoteSigned -file c:scriptsSSRSRecycle.ps1"

This command creates a new scheduled task named “SSRS Recycle”, which will be run non interactively with elevated rights as UserName. The task will be executed daily at 02:00 am starting from 1st of August 2011 and will execute a PowerShell script SSRSRecycle.ps1 located in folder C:scripts.

For details about schtasks you can take a look on MSDN Schtasks.exe.

As mentioned in the beginning, it is not real solution to the problem with recycled application domains, however it provides an acceptable work around and you will have every day reporting services ready and available without any delays.

 Posted by at 10:56 am  Tagged with:

  55 Responses to “Solving issue with first long starting report on SSRS 2008”

  1. Thanks Pavel, This is a very clever fix for this. Since my last question to you, I’ve set the recycle time to 3 days and started using some scheduled reports that run at 7am and I haven’t seen the problem again.

  2. Great work Pavel – you have the only elegant solution I was able to find that to this issue.

  3. Thanks a lot Pavel! This is working great and is the only real solution out there.

  4. Hi Pavel, first off thanks for this article it really helped me out.

    I had to go down a different route though for two reasons.

    1) This method required giving permissions to start and stop a service to a user whose password didn’t expire.

    2) The Recycle time is not the only reason SSRS recycles: see http://msdn.microsoft.com/en-us/library/bb934330.aspx

    Therefore I took the following approach:

    A) Find an event that is triggered when SSRS is recycled and make the scheduled task event driven. This eliminates the need to Stop and Start the service and caters for all other Recycle causes. Note, there is not clear event for this but I noted that the temp DB of SSRS has its compatibility level changed to 100 on recycle (this may be different per version but I’m sure the principle remains).

    B) Create custom XML for the event trigger. See below. It may be worth trying your custom XML in the windows event viewer first to make sure it works.

    C) Removed the stop/ start powershell from you script.

    D) I called the Powershell script from a .cmd batch (see below) and out put powershell results to a file to help in debugging as some users/ environments UAC conditions you can’t get task scheduler to call powershell directly.

    ***CODE***
    Put the following files into your scripts directory as mentioned in the code. I used C:DBASCRIPTSSSRSRecycle

    ***Powershell script “SSRSRecycle.ps1″ copy code below ***

    echo "starting pre-cache of SSRS site"
    $wc = New-Object system.net.webClient
    $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
    $wc.Credentials = $cred
    $src = $wc.DownloadString("http://localhost/Reports/Pages/Folder.aspx")
    echo "finished pre-cache of SSRS site"
    

    ***code ends above***

    ***batch file ssrs_master.cmd calls .ps1 and outputs to file***

    powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dbascriptsSSRSRecycleSSRSRecycle.ps1 > C:DBAScriptsSSRSRecycleoutput.txt

    ***code ends above***

    ***SSRSRecyle.xml, import this to create an event driven scheduled task***
    ***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***

    2012-03-09T14:26:20
    YourLogin

    true
    <QueryList><Query Id=”0″ Path=”Application”><Select Path=”Application”>*[System[Provider[@Name=’MSSQL$UAT’] and (EventID=5084)]] and *[EventData[Data and (Data=’COMPATIBILITY_LEVEL’)]] and *[EventData[Data and (Data=’ReportServerTempDB’)]]</Select></Query></QueryList>

    domainuser
    S4U
    HighestAvailable

    IgnoreNew
    true
    true
    true
    false
    false

    true
    false

    true
    true
    false
    false
    false
    P3D
    7

    c:dbascriptsSSRSRecyclessrs_master.cmd

    ***Code ends above***

    ***Custom XML event filter as used by the task above***
    ***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***

    *[System[Provider[@Name=’MSSQL$UAT’] and (EventID=5084)]] and *[EventData[Data and (Data=’COMPATIBILITY_LEVEL’)]] and *[EventData[Data and (Data=’ReportServerTempDB’)]]

    ***Code ends above***

    • Hi, you are rigth abou the Application Domain recycling. however the situation related to configuraiton changes can be predicted and you can schedule the changes or immediatelly query the RS service.

      The situation of hi memory presure and resources outage it not possible always predict but can be avoided with prooper reports design etc.

      Only the recycling after the specified period of time is regular and this is the issue if there is heavy use of the RS during business hours than the timer start since last recycle and next recycle can occure anytime during the business hours. Thi can cause that som euser will comlain about long runnning report even it is handled by triggered event.

      So the only way how to avoid regular recycling during business hours is to recycle and query the RS service out of bussiness hours so it is reqdy when the business day starts.

      Related to the premissions to start/stop the service. I do not see the issue as any administrative scripts you launch on the machine, launch under some higher priviledged account, so the script for the RS recycling can be launched under higher priviledged service account as other task. Also it is possible to grant stop/start only this particular service.

      Anyway, your approach is also good approach and each solution has it’s pros an cons.

  5. Hmm didn’t like my XML! Let me know how and it I can output it.

  6. […] Rozwiązanie 1: wydłużyć czas recyklingu oraz automat odpalany w nocy, który za nas będzie udawał pierwszego użytkownika: http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/ […]

  7. Pavel, I’ve used this on a Native Mode Reporting Services instance and it works great. I’m having the same problem however in a Sharepoint Integrated Mode environment and I can seem to get the script to work with this mode. Does it work for Sharepoint Integrated Mode?

    Thanks!

    Ryan

    • Hi,

      I didn’t tested this with service in SharePoint integrated mode, but I don’t see any problem using it even i SharePoint integrated mode.

      The only thing you will have to modify is the URL to be queried. Don’t know whether it is enough to query eg. URL of some SharePoint library with reports, or whether it will be necessary to open some kind of report.

      First I would try to query a library.

      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//mysharepointsite.com/Tests/ReportServerTest/Reports/Forms/current.aspx")

      If this will not be enough, the I would create a simple report with eg. single text box or query any of existing reports in any library.

      If I will have a little more time I will test this directly on some SharePoint site.

  8. Thanks Pavel. You’ve solved a long standing problem. Many thanks.

  9. Thank you Pavel, this is an interesting issue we’ve run into lately.

     

     

  10. Hi,

    Any idea why I get this error? It does work anyway, the reports page opens quickly after this has run but there is this timeout:

    PS C:WindowsSystem32> $wc = New-Object system.net.webClient
    PS C:WindowsSystem32> $cred = [System.Net.CredentialCache]::DefaultNetworkCred
    entials
    PS C:WindowsSystem32> $wc.Credentials = $cred
    PS C:WindowsSystem32> $src = $wc.DownloadString(“http://localhost/reports_ssrs
    jt/Pages/Folder.aspx”)
    Exception calling “DownloadString” with “1” argument(s): “The operation has tim
    ed out”
    At line:1 char:26
    + $src = $wc.DownloadString( <<<< "http://localhost/reports_ssrsjt/Pages/Folder
    .aspx")

  11. Fantastic! At long last you gave me a neat solution for a problem I had for a long time…

  12. I have it like this so I don’t have to worry about first requests as my script does that for me. Whenever the SSRS service recycles, it will do the warmup:

    1. Open powershell and run
    Set-ExecutionPolicy RemoteSigned
    Get-ExecutionPolicy

    Should return “RemoteSigned”. After this you can run powershell scripts locally

    2. Create a folder c:ssrs for example
    3. Create a file ssrswakeup.ps1 with these Pawel’s lines in it

    $wc = New-Object system.net.webClient
    $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
    $wc.Credentials = $cred
    $src = $wc.DownloadString(“http://localhost/reports/Pages/Folder.aspx”)

    Check the $src http address from SSRS Configuration Manager

    4. Create wakeup.cmd with this:
    powershell -command “& ‘c:ssrsssrswakeup.ps1′ ”

    5. Open Task Scheduler and create new:

    Name: SSRS Wakeup
    Account: add an account with permission to open url in $src
    Trigger:
    On an event -> Custom -> Edit Event Filter-> XML (@Name is your service name)

    <QueryList>
      <Query Id="0" Path="Application">
        <Select Path="Application">*[System[Provider[@Name='SQL Server Reporting Services'] and Task = 0]]</Select>
      </Query>
    </QueryList>

    Task = 0 means startup.

    Actions Start a program:
    c:ssrswakeup.cmd

    Other helpful:

    – SSRS Recycle time is in minutes (1440 min = 24h)

    C:Program FilesMicrosoft SQL ServerMSRS10_50.SSRSJTReporting ServicesReportServerrsreportserver.config
    <RecycleTime>1440</RecycleTime>

    – SSRS Logfile C:Program FilesMicrosoft SQL ServerMSRS10_50.SSRSJTReporting ServicesLogFiles

    • Step 5:

      5. Open Task Scheduler and create new:

      Name: SSRS Wakeup
      Account: add an account with permission to open url in $src
      Trigger:
      On an event -> Custom -> Edit Event Filter-> XML (@Name is your service name)

      *[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

      Task = 0 means startup.

  13. Damn,

    <blockquote cite"

    *[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

    “>

  14. Damn,

    <blockquote cite="

    *[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

    “>

  15. I cant use it :(

    <blockquote cite ="

    *[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

    “>

  16. Dear Pavel, Dear Everyone,

    Quick question for you guys. Does this problem occur for each individual user of SSRS, hence leading to fine-tune the solution for each user (sounds unlikely to me) or is it just a ‘global’ warm up that needs to performed with one single user (which is what I do believe).

    Thank you for your time and answers,

    Don

    • Hi,

      as you thought, the issue is related to the global warm up after the application domain is recycled. So it is necessary to do at least one request to the SSRS service. During the first request SSRS initializes all internal structures and caches and this takes the time.

  17. Hi Pavel,
    i used your script, but i get this error, could you help me please with this?
    Windows PowerShell
    Copyright (C) 2009 Microsoft Corporation. All rights reserved.

    PS C:WindowsSystem32WindowsPowerShellv1.0> C:AdminSSRSrecycleSSRSrecycle.
    ps1
    WARNING: Waiting for service 'SQL Server Reporting Services (MSSQLSERVER)
    (ReportServer)' to finish starting...
    WARNING: Waiting for service 'SQL Server Reporting Services (MSSQLSERVER)
    (ReportServer)' to finish starting...
    Exception calling "DownloadString" with "1" argument(s): "The operation has tim
    ed out"
    At C:AdminSSRSrecycleSSRSrecycle.ps1:6 char:26
    + $src = $wc.DownloadString <<<< ("http://server/Reports/Pages/Folder.aspx")
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    • Hi,

      The issue is that the Report Manager didnt’ start withing the timeout which the WebClient has. Unfortuantelly it is not possbile to set longer timeout for the WebClient without creating a new class and overide parent methods. But even this error has been thrown the initialization of SSRS started and after some while the the report response should be normal.

      To avoid this error it could be possible to handle the errors inside the PowerShell script, or use the HttpWebRequest class GetResponse instead of WebClient. The HttpWebRequest class has the possibility to setup the timeout. You can take a look on samples on MSDN – HttpWebRequest.GetResponse Method and HttpWebRequest.Timeout Property.

  18. I tried your solution, but I get:
    Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dnmRestartSSRS.ps1″ in instance “{f9449b39-316a-4c3a-b9b7-e1e8cdbdfec7}” of task “SSRS Recycle”. Additional Data: Error Value: 2147942523.

    Running the “powershell.exe …..” using the ‘run’ command works.
    Any idea?
    Thanks in advance!

    • Has the user under which you are trying to launch the scheduled task administrative rights? The powershell in the example is running with elevated administrative rights, so the account uner which it is executed has to have administrative rights on the computer.

      • Yes, I am using the domain admin account which has full admin rights on the server. I also checked the ‘Run with highest privileges’.

        • Can you post all the details from the Event Log related to this problem?

          • First I get:
            Task category: Action failed to start
            Description: Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dnmRestartSSRS.ps1″ in instance “{46caf0b2-50df-49ad-90d6-0f4f9fd203ae}” of task “SSRS Recycle”. Additional Data: Error Value: 2147942523.
            Then I get:
            Task category: Action start failed
            Description: Task Scheduler failed to start instance “{46caf0b2-50df-49ad-90d6-0f4f9fd203ae}” of “SSRS Recycle” task for user “customerdomainAdministrator” . Additional Data: Error Value: 2147942523.

          • Hi Pavel,

            Do you have any idea in which direction I have to look to solve this problem?
            Thanks,
            Rob

            • Hi, sorry for late answer, but the only thing I have found it seems to be related to the user credentials and/or UAC.

              Your account under which you are launching the script is local account or domain account? If it is a domain administrator You could try to add this account directly to the local administrators group.

              Other option could be granting the user to start/stop the SSRS service even without elevating administrative rights.
              For this you can ceck this techned thread Using AD to allow a user to start/stop a service

  19. Hi Pavel i have tried your solution but cant get it to work on my server the script runs fine and restarts reporting services no errors when opening the reports folder, i have scheduled the recycle time to 1500 and have the job running at 2 in the morning everyday. But still the first report of the day takes nearly 6 minutes and then reports after less than a minute. Any ideas i have tried subscribing to a report in the reports folder which fires every 20 mins but that doesn’t make a difference either.

    • Does it make a difference that the report is embedded into a web site and we are not running the reports direct from SSRS?

    • Hi, I would try instead of querying the report manager, the report itself directly. If this will help, than your problem will not be related only to the SSRS application domain restart, but can be related also to the caching on the source database side etc.

      You can also review the [dbo].[ExecutionLogStorage] to check execution details of the report processing like Data Retrieval Time, Processing or Rendering times.

      • I have queried the [dbo].[ExecutionLogStorage] and noticed that the TimeDateRetrieval is very high when running the first report of the day, processing time is about the same all the time and time rendering is sometimes higher on the first run.

        • So it seems to be the report query issue. Once queried, the data are being cached and following executions are fetched from the DB engine case. When not used for longer period of time, the data are flushed from the DB engine cache.

          You should take a look on the query execution plan and try to fine tune the query to lower the execution time.

          If it is not possible, then depending on the report you can try to utilize report caching or report snapshots. See MS TechNet Performance, Snapshots, Caching (Reporting Services) for details.

          • Could i run the query’s which populate the reports via a sql job in the mornings thus caching them so when users run the reports they shouldn’t get the wait?

            • Better to use the Caching mentioned in previous answer (this will do the job of executing the query and cache the data so sub sequential execution will use the cache) and of course.. if possible, fine-tune the query.

              • As the reports use parameters where the values change is caching still an option?

              • Yes, it is possible to cache parameterized reports but it depends on the report. See the link I’ve posted in previous answers.

                Anyway first I suggest to focus on optimizing the query whenever possible.

  20. Hello Pavel,
    The SQL job is a great workaround but Is there a more permanent solution to this problem like applying any changes to the settings on the server side as supposed to creating and maintaining the sql job.

    Thanks.

    • Hello Pavel,

      I tried to implement the solution you have mentioned in this article to get past the same issue we are having in our SSRS report server.

      I have scheduled a task to execute the Power shell script everyday at 3:00 am and set the recycle time to 1500 minutes in the reportserverconfig.

      The last step in the Power shell script where we redirect to the download string is not successful…It always shows the logon form to authenticate the first time.

      Here is the script in the powershell I am executing;

      Stop-Service SQL Server Reporting Services (SSRS_DEV)
      Start-Service SQL Server Reporting Services (SSRS_DEV)
      $wc = New-Object system.net.webClient
      $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
      $wc.Credentials = $cred
      $src = $wc.DownloadString(https://ssrsdev.contractlandstaff.com/ReportMgr_DEV/Pages/Folder.aspx)

      I wanted to know if there is something I can do so that the logon form would not pop up.
      I will appreciate your response.

      Thanks,
      Sangeeta

  21. Hi,
    I have a weird problem, I have about a suite of 200 reports and only one of them takes a lot of time to even load (in BIDS as well), I can not understand why only this one takes such a log time, I has 5 datasets and 4 parameters(which is not much)

    Thanks,
    subash

    • Hi,

      Does the long starting report uses a DropDown for parameters? If there is a quite a lot of rows in the drop down parameter, than the report will start much longer. Event the query for the parameter returns the records immediately.

      You can also take a look into the ExecutionLogStorage table in the Report Server DB for details what consumes the time.

  22. Hi Pavel,
    Thanks for your solution, it’s great to work around with SSRS first start issue. It’s really effect in Report Manager that it’s load better in first time. But when I apply to actual project with MVC app using ReportViewer to show report, it’s still take long time in the first time. I dont know why or I’m wrong in some thing? I also tested instead of calling to ReportManager url, I tried to call to an empty report (e.g: “http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fReports%2fReportEmpty”) but still take long time when firsttime load report in my web app. Do you have any idea?

  23. Great posts and comments.

    Any sample ps1 script about modify RecycleTime value in the rsreportserver.config ?

  24. […] for the slow initial startup time. As nicely described in Pavel Pawlowski’s blog post (http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/), the complete solution involves increasing the time between resets and also scripting out an […]

  25. Hi Pavel,
    I have a Windows desktop application which has the same problem, and I have used a similar approach to fix it. I have made a batch file which calls the stored procedure which is used in the application. It takes about a minute to display all the result in cmd. Assuming that SSRS has loaded all the required settings, I run the application, but still it takes a lot of time to load and then gets faster on next load.
    How couldnt this work?

    • Your issue will be related to cold cache. When you ring your application for first time ti take long as SQL Server has to load all thedata from disk. Subsequent starts runs the queries agains cached data on the SQL Server side and therefore runs faster.

      I would focus on optimizing tasks the stored proc is doing to minimize the stored proc runtime.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>