SSRS report never stops loading

saanah khan 21 Reputation points
2021-02-08T16:50:35.783+00:00

I have a report which was working correctly and is still working correctly for all other clients except for one. the report just keeps loading and loading. When I hardcode the date values, the report runs in 40-50 seconds. The query against their db takes 30-50 seconds. I created a new report and took off all filters except for the date ones and removed all grouping and limited the report to only show 2 rows per page. Even this basic altered version of the report does not run. I added the link to the internet for compatibility too and it still doesn't run .

Here is the additional information from the execution log

<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>1</Pagination>
<Processing>1078112</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>1</SQL>
</DataExtension>
<Connections>
<Connection>
<ConnectionOpenTime>1</ConnectionOpenTime>
<DataSets>
<DataSet>
<Name>dsMain</Name>
<RowsRead>437384</RowsRead>
<TotalTimeDataRetrieval>77826</TotalTimeDataRetrieval>
<ExecuteReaderTime>59642</ExecuteReaderTime>
</DataSet>
</DataSets>
</Connection>
</Connections>
</AdditionalInfo>

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,818 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 33,386 Reputation points
    2021-02-09T07:32:45.17+00:00

    Hi @saanah khan ,

    Can you run the report via SSDT/Report Builder locally?

    Double check you have both catalog and system level permissions on the report.

    Try to modify the credentials used to datasource, maybe set to stored credentials to see if that works.

    In addition, check the Execution log to see which period it spent most like here say.

    Also see the error log for more details.

    You may use SQL Profiler to catch the trace to do more analyze.

    Regards,
    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November

    0 comments No comments

  2. saanah khan 21 Reputation points
    2021-02-09T16:01:18.067+00:00

    Zoe the report does not run for that client on the report server or on their live environment.

    I looked at that microsoft page and added page breaks since my report is taking long in the processing stage but it still does not help.

    The credentials are stored and all other reports are working properly.

    0 comments No comments