When running reports, you may see in the logs as if the timeout period has expired and the server is not contactable. All sorts of strange things may occur. When an initial report is executed a user's session is locked in order to create the temporary snapshot data into the tempDB (ReportServerTempDB). This is normal behaviour. However, reports can sometimes, for various reasons take an extremely long time to run. Because of this, in some cases the user's session can timeout before the full report is executed resulting in odd behaviour and a multitude of different errors.
When we process reports, we don't use the live reports 'ReportServer$SQLExpress.mdf' database. We use cache reporting snapshots using the tempDB ReportServerTempDB.
Overview of how to improve performance:
- Disable the Idle-timeout on the reportserver application pool in IIS on the performance tab
- Confirm ant-virus is not running
- Check the performance counters MSRS 2005 webservice and MSRS 2005 web server
Troubleshooting tips:
Reports server logs are located in the following directory:\Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles
Increasing the report execution timeout:
This can be done using ReportManager. So you need this installed. (on your local host: http://server/reports$sqlexpress).
- Select the 'site settings' at the top of the page.
- Change the 'limit report execution timeout' to 6000 or something like that.
- Click apply
Enabling verbose logging for reporting services:http://msdn2.microsoft.com/en-us/library/ms156500.aspx
If the report server ReportServerTempDB has grown uncontrollably:
- Restart the SQL services. If this does not work, please continue
- Delete the database
- This database must then be re-created. To do this either re-install reporting services run the following script:
X:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\CatalogTempDB.sql
- Then do an IISRESET and restart the SQL server reporting services service
How to increase the user sessions timeout for reporting services:
Save the following code as sessiontimeout.rss to c:\:
Public Sub Main()
Dim props() as [Property]
props = new [Property] () { new [Property](), new [Property]() }
props(0).Name = "SessionTimeout"
props(0).Value = timeout
props(1).Name = "SessionAccessTimeout"
props(1).Value = timeout
rs.SetSystemProperties(props)
End Sub
You can run this script with the following command from cmd prompt: rs -i C:\sessionTimeout.rss -s http://localhost/reportserver$sqlexpress-v timeout="6000"
