Sql-server – SSRS executionlog having rowcount 0

sql serverssrs

I'm reviewing some execution logs for long running queries in SSRS.
A common thread I'm seeing is reports that seem to take a few hours to process, with a rowcount of 0.

I'm using the following query to get my results:

SELECT e.instancename, 
   e.username, 
   e.timestart, 
   e.timeend, 
   Datediff(mi, e.timestart, e.timeend) AS 'Minutes', 
   e.timedataretrieval, 
   e.timeprocessing, 
   e.timerendering, 
   e.[RowCount],
   catalog.PATH, 
   catalog.name
FROM   executionlogstorage e  
   INNER JOIN catalog 
     ON e.reportid = catalog.itemid 
   INNER JOIN users 
     ON catalog.modifiedbyid = users.userid 
   INNER JOIN users AS users_1 
     ON catalog.createdbyid = users_1.userid 
WHERE  Datediff(mi, e.timestart, e.timeend) > 15
   AND catalog.name <> '' 
   and TimeStart > 01-01-2014
   -- Filtering out Oracle reports--
   and (TimeDataRetrieval + TimeProcessing + TimeRendering + [RowCount]) <> 0
ORDER  BY 'Minutes' DESC

And one of the results I'm not sure how to translate is:

|Minutes | timedataretrieval | timeprocessing | timerendering | RowCount |
|243 | 3 | 14453530 | 0 | 0 |

How is it possible to have a report processing for 4 hours, and return 0 rows, with a dataretrieval time of 3 miliseconds?
If this were a lone case I'd ignore it, but I've got a hundred similar ones. (and a few thousand ones that do make sense)

Best Answer

This has happened to me before when the InteractiveHeight was set to 0. The query only returned a few thousand rows, but since there was no paging in the report, SSRS was trying to put everything on a single page. Eventually I would hit the SSRS timeout before it reached the rendering phase, and have 0 rows returned.

If that doesn't apply to your situation, please check out this link for some additional troubleshooting ideas: http://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx#Process

Related Question