Posted by beakersoft | Posted in SQL Reporting Services | Posted on 01-05-2008
I’ve been doing a bit of tweaking on a SQL Reporting Services install this week, trying to get it to run a bit better, get better logs out of it and generally try and improve its performance. So, what did I do?
Reports Timing out – This is a pain. I have a couple of reports that people run huge queries for, and sometimes the reports can time out while running. This was a fairly easy fix, just locate your rsreportserver.config file (mine was in (Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer). Open it up and alter the key:
DatabaseQueryTimeout- This is the amount of time the reports server will wait until it thinks the query to the server has timed out. You can set it to 0 (zero) for no timeout, but this is probably not a good idea. There is another key in there I thought might also help called SQLCommandTimeoutSeconds but according to the MS docs this is not used (so why the hell is it in there?)
Better Client Side Errors – Fault finding errors in the report viewer can be a bit painful, but you can enable remote errors to help you out a bit.
There is a table in the reports server database called ConfigurationInfo. In here there is a EnableRemoteErrors property. If you set this to true and restart the reporting service clients will get better errors, helping you try and work out what is actually happening.
My next step with errors is to try and handle them properly in the reports. There is a ReportError event witch I think will do the trick. When I have worked out the best way to use it i will post about it here
Logs – As well as writing to the application event log, reporting services also write more detailed information into its own log files. These (along with crash dump files) can be found in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles.
Using all this info I now find it easier to troubleshoot and tweek my environment. If anyone else has any handy hints let me know and ill add them to this post!