Building a script to restart REPORTING SERVICES automatically

  • Article

Sometimes it can be useful to restart Reporting Services automatically using a script to avoid the initialization time it takes to start Reporting Services the first time it is being accessed, or to control the recycle process of the web service.

 

The following article explains how Reporting Services performs the recycle operations (planned or unplanned):

Application Domains for Report Server Applications

https://msdn.microsoft.com/en-us/library/bb934330(v=sql.105).aspx  

 

Additionally it could be possible that the recycle process happened when the memory pressure is high. More information in the following article:

Configuring Available Memory for Report Server Applications

https://technet.microsoft.com/en-us/library/ms159206(v=sql.105).aspx

 

 

IN SUMMARY:

Reporting Services performs a recycle of the Report Server service every 12 hours (by default) depending on the value RecycleTime in the configuration file.
It is not possible to know the time in which the next recycle is going to happen.

An alternative could be implementing a script that restarts Reporting Services (for example every night at the same time) and, once restarted, accesses the Report Server to refresh the cache.

 

BUILDING A SCRIPT TO RESTART REPORTING SERVICES:

A good approach would be to control the recycling process and avoid it from happening during the execution of your reports/subscriptions/scripts would be to create a script
in PowerShell that:

 

  1. Restarts the Reporting Services Service
  2. Then wakes up the Report Manager to avoid the extra time it takes to load the page the first time is used.

 

The script could look like this (in PowerShell):

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

 

Note: You need to substitute localhost by the name of your server

You will run this script at the same time every day.

 

To automate the PS script to run periodically we can use the follow command: schtasks

You can find more Information at: https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/schtasks.mspx?mfr=true

Or simply create a job in SQL Server to run the script.

 

PERMISSIONS:

In the following link you can see the necessary permissions by the account that runs the rs script: (https://msdn.microsoft.com/en-us/library/ms162839(v=sql.105).aspx)

 

 

I hope it helps,

 

Maria Esteban

Reporting Services Support Engineer