Report Server issues

I’ve had a lot of Report Server issues in the last few months. I thought we solved it once, only to see it come up again in a slightly different way. But I think I can put it all to bed now.

The first time was a few months ago, right when I left on vacation (isn’t that always the way?). The C drive on one of our reporting servers began to run out of space, filling up 6 – 8 GB in a matter of hours.

Before I get too far ahead of myself, let me describe our layout. We have four regional databases running SQL 2005 Standard Edition, each with data for that region. Two regions are in our corporate headquarters, the other two are in my office. We also have two SQL 2008 Standard Edition servers that run in a virtual environment, one in each datacenter. The reports hosted on each reporting center are identical except for their data sources; the corporate report server fetches data from the two corporate databases, and the local server does the same. The issue we had was on the server in the corporate datacenter.

Our first step was to check the temp files. We noticed a few small files and one massive, 8 GB file that we couldn’t delete without stopping and restarting the report service. A few hours later the massive file reappeared. All of these temp files were prefixed “RSTemp_”.

We moved the location of the temp files to a larger drive to give us more time before the largest temp file grew too large. By default the location is on C:\Program Files\Microsoft SQL Server\MSRS10.<<Instance Name>>\Reporting Services. You can change it by modifying the rsreportserver.config file and adding the entry to the <Service> tag:

    <Service>
      ---
        <FileShareStorageLocation>
            <Path>I:\RSTempFiles</Path>
        </FileShareStorageLocation>
    </Service>

That gave us extra space, but it didn’t solve the issue. A little more digging showed that we had a long running report that was also getting bad data. The query for the report was looking for a scalar value but was returning multiples. The query was also a long running query. What we found was happening was that the report execution was timing out, but the query was still active. The user didn’t see the timeout and constantly tried to re-run the report. Consequently this report was continuously paged out to disk, and it never died. Once we cleaned up the data and fixed the reports query, the problem hasn’t reappeared.

The next issue was related to performance. We noticed at certain times of day the report server would get really sluggish. Reports that would complete in 10 seconds would now take 10 minutes. Looking at the server resources showed no increase in CPU, IO, or memory usage. We didn’t see a repeat of the super-big temp file, though there was an increase in the number of temp files.

A little more investigation showed that, when the performance began to degrade, there were locks being held by one process clearing the sessions that blocked another process that was writing session data. We were finally able to track this to a third party application we use to monitor performance. It didn’t look like the software was configured properly.

In investigating both of these issues we looked at the data in the ExecutionLogStorage table in the ReportServer database. If you’re not familiar with this table, you should check out Robert Bruckner’s excellent post on ExecutionLog2 View - Analyzing and Optimizing Reports.

In the first issue, we were able to identify the report by seeing the pagination values in the additional information data. In the second, we could spot when the slowness would begin by seeing the TimeProcessing values rise.         

What I learned today – Report Server Execution log package

My first attempt at a data warehouse is going to be collecting data from our report servers. In our environment we have two SQL 2008 instances that host the reports themselves, and they connect to four data centers for the report data. In the data centers we have a table that’s populated with parameters needed for each report; dates, locations, etc. The reports themselves only have two parameters; the datacenter where the data for the report is (we’re using dynamic connection strings) and a guid that identifies the other needed parameters in the data center.

My goal was to build my warehouse from the four data center report parameter tables and the Execution Log tables on the report servers. The report server logs information from each time a report is run; if it was successful, the parameters for the report, the user, and more that would be helpful in debugging a report performance. I wanted to be able to view summaries for each report; for instance how often each report was run for each location during a specified date range, average report durations, or the number of aborted reports.

I was going to build an SSIS package to gather the data for me. Then I read an article in the November 2009 issue of SQL Server Magazine by Tyler Chessman that defined about half of what I want to do. In his article SQL Server Reporting Services Questions Answered, Mr Chessman describes sample reports from Microsoft that you can find on CodePlex. The reports will be interesting enough and I’ll be able to use them, but the best part is Microsoft has already created a package to extract the data from the report server execution log!

This post is meant to be an overview. I’ll post a review of the CodePlex samples soon, and I’ll start laying out my data warehouse design.

Lessens learned – Report Server administration

It’s a long commute in the morning, so I thought I’d use this time to compile a few things I learned and noticed about administering a server hosting SQL 2005 or 2008 Report Services.

• Check the recovery model of the databases you create on your report server, including the ReportServerTempdb (or whatever you named the SSRS temp database). By default they will be in full recovery mode unless you’ve changed your model database. If they are still in full, the log files will grow and may cause you space problems later. Consider using simple mode. If you use full remember to schedule log backups.
• Backup the SSRS tempdb as well as the database holding your catalog. You’ll need this database if you need to restore the server, it’s not recreated when SQL starts up like tempdb. I’ve seen a few maintenance plans that skip this.
• Speaking of backups, remember to backup the SSRS encryption key. You’ll need that key if you ever want to restore the catalog database and you don’t want to re-enter all encrypted content, like data source credentials. I’ve successfully moved catalogs from production to a testing server using the key to restore the connections. You can backup the key from the Report Server Configuration Manager. I suggest that you also copy the backup somewhere safe in case you can’t get to the server.
• If you don’t want your reports exported in a specific format then disable that format. For instance, not a lot of reports look good if you export them in a comma delimited format. Just remove or comment out the entry in the rsreportserver.config file. You’ll find this file in the path for your<instance path>\Reporting Services\ReportServer. Be careful removing the image format – this could cause some problems, especially if you open the report in the report viewer control available in Visual Studio.

<Render>
            <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>
            <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
           <!-- <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/> -->
            <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>
            <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
            <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>
            <Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>
            <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering"/>
            <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
</Render>





• You’ll find some good information in the ExecutionLogStorage (SQL 2008) or ExecutionLog (SQL 2005) tables. These tables can help you troubleshoot problems with the report as it shows the name of the person running the report, the time it took for retrieving data or rendering the report, the rendering format, and any parameters used. It will also show if the report was opened successfully or if it couldn’t open because of a rendering issue.



• Another place to look at during troubleshooting is the logs. You’ll find these at <instance path>\Reporting Services\LogFiles. You may need to clean these files up on occasion since some report errors will generate dump files.

Report Service error

Yesterday I helped a developer troubleshoot an issue he was experiencing with a report. The report is hosted on a SQL 2008 instance used for testing and was being viewed in a Win Form application using the Report Viewer control. We congigured the viewer control opens the reports as an image since this would be what the client sees when they print it. He couldn't tell me the exact error message but it referred to not being able to find the stream. He also stated that the report worked fine if he ran it with less data.

I first checked the execution log table in the report database and I saw he was getting a rendering error. Next I had him rerun the report in the viewer. This time he received a generic error saying that there was a problem. But if he clicked the page layout button to render the report in HTML the report ran fine.

My next step was to check the logs on the report server instance. Sure enough I found the error. It was an IO issue; not enough space on the drive. Luckily this was on a testing server and not production.

Now the testing server is not set up the same as a production server. This box is a jumble of programs and databases, running multiple SQL instances for 2000, 2005, and 2008. The report server temp database is on the same drive as the report server database and the error logs and dumps. At the time I saw this the drive had about 100 MB free. And while 100 MB isn't a lot of space nowadays, I would have thought it would have been enough to run the report (it had over 800 pages when we freed up space for it).

At this point I'm not sure where that image is being produced, in the reports database or the report temp database. I've never dug into the report temp db before. I'd like to head from anyone who has had similar problems, or can show me where else I should be looking.

Updates to previous posts

In my posts on virtualization I forgot to mention something. While I did say I was running with the multi-thread option instead of the replay in order option, I left the number of threads at the default of 4. I ran it with 64 threads earlier, but with the replay in order option. Those replays took well over 5 hours, as opposed to 2 1/2 – 3 hours with multi-thread at 4. But if I compare multi-threading at 4 with multi-threading at 64, then the times are much better – 3 1/2 hours compared to 2 3/4 hours on the same virtual server. I’ll summarize all my tests in a few weeks when I’ve completed all my tests.

The second post I need to update regards repeating data in a page header using SSRS. I solution was to use a shared variable. This had potential problems which I neglected to mention. If you use a shared variable then anyone running the report at the same time could overwrite each other values. I knew this at the time I put the report into production, but I thought the odds of that happening would be slim. Later I found I could produce that exact behavior in testing; when I ran the report simultaneously with another tester we did indeed cross values.

I changed the report to use a hashtable instead. The value in each record would be unique since it will depend on the user id of whoever is running the report. As an example of repeating a customer name on each page I

1. Added a hidden row to the top of the table control on the report. The table was already there and the grouping was already set on the customer name.
2. Added the customer name field from the table’s dataset to the hidden row.
3. In the report code section I added a shared hashtable – DIM htCustomer AS system.collections.hashtable = NEW system.collections.hashtable. The hashtable still needs to be shared.
4. Still in the code section, I added a new function to add a record to the hastable if it didn’t exist or update it if it did. The function takes three parameters; the value from the hidden field, a group name, and the user id. It returns the customer name for the current user

DIM key AS STRING = groupName & UserID
IF NOT group IS NOTHING
   DIM g  AS STRING= CTYPE(group, STRING)
      IF NOT (htCustomer.ContainsKey(key) THEN
          htCustomer.Add(key, g)
      ELSE
          IF NOT (htCustomer(key).Equals(g)) THEN
              htCustomer(key) = g)
          END IF 
      END IF
END IF
RETURN htCustomer(key)

5. Add a page header to the report. Add a new text box that uses an expression to call the function and pass in the parameters

=Code.SetCustomerName(ReportItems!txtCustomer.Value, “Customer”, User!UserID)

ReportItems!txtCustomer is the hidden field; Customer is my group name, and User!ID is a global parameter available to the report.  

Moving a reporting database

Another task I just finished in regards to SSRS was moving the reporting database. We have an older server we were using for developing the report on. The system team gave us a virtual server (we're going to run virtual when we go live). I wanted to copy the existing catalogs to the new server so I wouldn't have to re-deploy them all. These are the steps I took. In my case the new report server was already installed and configured

1. Backup the encryption key on the source server using the Report Server Configuration Manager. This should be done regardless.
2. Backup the reporting database on the source server.
3. Restore the backup from the source to the destination server.
4. Apply the encryption key from the source onto the destination.

After I completed these steps all the reports and their data sources and credentials appeared on the new server's Report Manager. Hopefully I haven't missed anything.

Getting data into a page header in SSRS

As I've mentioned before, we're moving MS Access97 reports to SSRS 2008. Recently I came across another issue, repeating data in a page header. You may or may not know it, but you can't directly add a data field to a page/report headers and footers. If you want your data to repeat you need to employ a workaround. One way is to use parameters and use a second data set for the parameters. I tried this method, but I had a problem when the page changed; the header didn't always mirror what was in my group. I also tried group headers that repeated on each page, but the report I'm working with also has sub reports that may or may not span pages. I found when that happened the group headers didn't repeat properly.

The solution I came up with is to set the values in code. The first step was to declare a public variable for each field I needed on my page header. The second step was to create a function that set the value of the variables to a parameter passed into the function. I then added a hidden text box on the body of my report that called my functioned and passed in the values I needed on the page header. Finally I added the page header section, added a text box for each field I needed, and used an expression to call the public variable I declared in the first step.

This isn't my invention; there are examples of this on the web. I don't have a source handy but I'll cite a few that I used later.

Reporting Service "features"

I've been a proponent of Microsoft's SQL Server Reporting Services (SSRS since 2005) since it was first released in beta for SQL 2000. I thought the product was a natural, combining a report writer with the database. I especially loved the ease of deploying individual reports. The first product was rough, but I figured it would improve with time. By the time SSRS 2005 came out it was a stable product, but it lacked some features, like rich textbox formatting and exporting to MS Word. SSRS 2008 added those features, so I thought all was right with the world. Silly me.

My client has been using some reports written for SSRS 2005. Recently we began converting their existing reporting application from MS Access 97 (please don't snicker too loud) to SSRS 2008. We started with small, simple reports. These worked out nicely, and developers were able to learn how to use the report designer fairly quickly. I began converting a much more complex report that had tons of Access code-behind and sub reports. Much on the report is determined by what the user wants to see. Creating the report in SSRS 2008 was time consuming, but it worked out nicely.

The "features" came out after I created the first, main report and checked the reporting project into Visual Source Safe. After that I started running into all kinds of problems, some of which are;

○ Very often, with no reason, credentials for the data sources would change from what we use to no credentials. I first noticed this after I checked the solution out of VSS. At first I thought the last person to check out the file changed it for some reason. Then I noticed that it didn't matter who last touched the file; even files that were never checked out were affected. And this also happened to files that were never checked into VSS.
○ Another annoyance is when a dataset changed from running a stored procedure to running text. This can happen if you modify the data source (see above), and also happened in SSRS 2005 if you merely opened the dataset definition to see what it was pulling.
○ Inconsistent behavior. For instance, the main report has 2 very similar sub reports. The user can choose to see sub report A, sub report B, or neither. Simple, right? Just set the sub-report visibility to display based on the user's choice. It worked fine if the user chose A or B; the correct sub report was displayed and the correct sub report was hidden. But if the user chose to hide both, then both display. I'm not going to post the code here, but suffice it to say the code was correct; it's being used on many other sub reports on the same main report. So how did I fix it? I moved the code to a report function. The bottom line is the very same code that didn't work as an expression of the visibility property of the sub report worked correctly when called as a function!


There's more, but this is enough for this post. I'll be adding to it soon.