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:
1
2
3
4
5
6
| 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 |
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.
1
| 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:\scripts\SSRSRecycle.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.
QR Code - Take this post Mobile!
Use this unique QR (Quick Response) code with your smart device. The code will save the url of this webpage to the device for mobile sharing and storage
Put the following files into your scripts directory as mentioned in the code. I used C:\DBA\SCRIPTS\SSRSRecycle
echo"starting pre-cache of SSRS site"$wc=New-Objectsystem.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"***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***
YourLogin
<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>
S4U
HighestAvailable
true
true
true
false
false
false
true
false
false
false
P3D
7
***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***
<preclass="brush: xml;"> </pre>Stop-Service"SQL Server Reporting Services (MSSQLSERVER)"Start-Service"SQL Server Reporting Services (MSSQLSERVER)"$wc=New-Objectsystem.net.webClient$cred= [System.Net.CredentialCache]::DefaultNetworkCredentials$wc.Credentials =$cred$src=$wc.DownloadString("http//mysharepointsite.com/Tests/ReportServerTest/Reports/Forms/current.aspx")PS C:\Windows\System32> $cred = [System.Net.CredentialCache]::DefaultNetworkCred
entials
PS C:\Windows\System32> $wc.Credentials = $cred
PS C:\Windows\System32> $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")
Set-ExecutionPolicy RemoteSigned
Get-ExecutionPolicy
3. Create a file ssrswakeup.ps1 with these Pawel’s lines in it
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(“http://localhost/reports/Pages/Folder.aspx”)
powershell -command “& ‘c:\ssrs\ssrswakeup.ps1′ ”
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><QueryId="0"Path="Application"><SelectPath="Application">*[System[Provider[@Name='SQL Server Reporting Services'] and Task = 0]]</Select></Query></QueryList>c:\ssrs\wakeup.cmd
<RecycleTime>1440</RecycleTime>
i used your script, but i get this error, could you help me please with this?
Windows PowerShellCopyright (C) 2009 Microsoft Corporation. All rights reserved.
PS C:\Windows\System32\WindowsPowerShell\v1.0> C:\Admin\SSRSrecycle\SSRSrecycle.
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:\Admin\SSRSrecycle\SSRSrecycle.ps1:6 char:26
+ $src = $wc.DownloadString <<<< ("http://server/Reports/Pages/Folder.aspx"
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
WebClienthas. Unfortuantelly it is not possbile to set longer timeout for theWebClientwithout 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.HttpWebRequestclassGetResponseinstead ofWebClient. TheHttpWebRequestclass has the possibility to setup the timeout. You can take a look on samples on MSDN –HttpWebRequest.GetResponse Method and HttpWebRequest.Timeout Property.Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:\dnm\RestartSSRS.ps1″ in instance “{f9449b39-316a-4c3a-b9b7-e1e8cdbdfec7}” of task “\SSRS Recycle”. Additional Data: Error Value: 2147942523.
Any idea?
Thanks in advance!
Task category: Action failed to start
Description: Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:\dnm\RestartSSRS.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 “customerdomain\Administrator” . Additional Data: Error Value: 2147942523.
Thanks,
Rob
For this you can ceck this techned thread Using AD to allow a user to start/stop a service
Thanks for your reply. I found the solution. The task manager will not run powershell on 64bit properly. You need to point directly to the 32 bit version, which is located on C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe. (see alsohttp://social.technet.microsoft.com/Forums/en-US/ITCG/thread/e298d613-47b8-4492-92d1-0b55cc8497c1)
So in the ‘edit action’ window (of the scheduled task) I entered this in the ‘Program/script’ textbox, and the other parameter you mentioned, in the ‘Add arguments’ textbox. Now it works!
[dbo].[ExecutionLogStorage]to check execution details of the report processing like Data Retrieval Time, Processing or Rendering times.