Sql-server – SSRS report stuck on Loading but ExecutionLog says otherwise

sql serverssmsssrs

Loading

I have a report that is stuck on loading in the reporting server portal (it's been over a day). However when i check the ExecutionLog…

select * from ExecutionLog3 
WHERE UserName = 'SOMEREPORTSERVER\SomeName'
AND ExecutionID = 'cbk0hdnpzrspg5qpkvqxubyn'
order by TimeStart desc

…it says that the status is 'rsSuccess'. Results

Here is the AdditionalInfo XML which might help… The query took about 1 minute to run which is how long it took to run in SSMS. Rendering seems pretty fair with the amount of data returned so I am not sure what's going on.

I made sure that Interactive Sizing is standard 8.5 x 11 so that it can show at least some data while processing the other pages but that did not help.

<AdditionalInfo>
  <ProcessingEngine>2</ProcessingEngine>
  <ScalabilityTime>
    <Pagination>0</Pagination>
    <Processing>0</Processing>
  </ScalabilityTime>
  <EstimatedMemoryUsageKB>
    <Pagination>263471</Pagination>
    <Processing>3795406</Processing>
  </EstimatedMemoryUsageKB>
  <DataExtension>
    <SQL>2</SQL>
  </DataExtension>
  <Connections>
    <Connection>
      <ConnectionOpenTime>24</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>nocontacts</Name>
          <RowsRead>79650</RowsRead>
          <TotalTimeDataRetrieval>10297</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>10225</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
    <Connection>
      <ConnectionOpenTime>30</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>contacts</Name>
          <RowsRead>724715</RowsRead>
          <TotalTimeDataRetrieval>70893</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>26506</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
  </Connections>
</AdditionalInfo>

Edit: SSRS – 14.0.807.134 / SSMS – 14.0.17177.0 / Browser – Chrome
Edit: Trying IE – 11.309.16299.0 – I don't have the access to update report viewer settings.

Best Answer

Sometimes the browser will time out or otherwise not return a rendered report even though the process is still running in the background and ultimately succeeds. The browser will likely not even tell you it failed, it will just keep spinning or return a blank page. The logs are indicating "success" meaning the process itself did indeed run correctly, but the logs won't tell you if browser successfully returned a rendered report. I've usually seen this happen around the 10 minute mark, regardless of the configuration of server or report level timeout settings. Your Execution Log shows it as over 10 minutes, so this is the possible reason. Also, the Execution Log returned a massive Byte Count (~353 MB), and this may also be a culprit.

There are a few possible solutions to this problem:

  1. Limit the amount of data that will be rendered in browser using pagination. The Data Retrieval & Processing time should stay the same, but the Rendering time will be reduced as it is only returning the first page, and will only render other pages when you navigate to those using the arrows. Reflecting back on the aforementioned massive Byte Count, so somewhere along the way it may have broke the camel's back. Once the data is processed, exporting to a static format, such as Excel or PDF, will also likely be successful & should throw an error if it failed, rather than it attempting to render the entire report in browser as HTML.
  2. Deploy the report in the export format to a File Share using a Subscription. This will allow the process to run in the background on the server and render it to a file directly. If you are unable to schedule this on a regular interval, this may be a bit inconvenient for the user to request it from an admin and require that admin to manually deploy it. However, I believe this is better than the report user rerunning the same report multiple times, resulting in concurrent executions, further compounding processing issues, affecting all users.
Related Question