Report execution seem stuck in the runningjobs queue

Question

I have a rogue report that seems to be running multiple times in the background and appears in the dbo.runningjobs queue. Even after closing the SSRs web page to stop the report , the entry does not clear from the table. Only when I delete the Report from the SSRs Server , it seems to clear.

This issue is with a particular report.

I deleted the entry in the dbo.runningjobs table but it comes right back with the same guid ...

As long as there is an entry in the table as above, I cant seem to be able to drop the Report.

So , I have to wait until the entry disappears ( my timeout for RS  execution is 100 minutes)  in the table and then drop the subscription on the report and then only then I am bale to delete the report from the server.

Is there a better way to handle this ?

 

---

SQLCHANTer

Answer 1

Have you tried tom optimize the query? How much data do you return?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

The query itself runs in under 6 minutes and returns about 90 MB of data as a csv file drop in the subscription., say 7 AM , but then I see the report execution is listed in the dbo.runningjobs even at 9.30 AM.

\

 

---

SQLCHANTer

Answer 3

I have this problem report that seem to be running multiple times and none of them complete and seem to be stuck. Dud to the hard coded thread limit of 4 reports for core , the queue if longer than 32 ( in my case ) , seem to be affecting other subscriptions as well. So I want to kill the long running subscriptions. Is there a way I can do that gracefully ?

------------------------------------------------------------------------------------------------------

JobID StartDate
2jjuhz45yykv0bbympcsirru 2013-08-05 07:34:52.137
2q5itjaojp0mlyq3bi03fnab 2013-08-05 07:34:52.057
4cf1vjzlccsjjjzjvf5zzz45 2013-08-05 07:34:52.057
5h4rammq3vxjkti3n1xqaj55 2013-08-05 07:27:25.680
apgqp155uvlmu545xfw1uj55 2013-08-05 07:34:52.073
cdgmptzk5f3ajb45peo0k2mx 2013-08-05 10:32:03.173
e1rxfu55kxih3k55aexvth45 2013-08-05 07:34:52.090
i4ny5055xut5gbqedoyysl55 2013-08-05 07:34:52.057
j5dzxg45vrogmtjpzbnmiszq 2013-08-05 07:34:52.027
lspo3xnna3o45p45zwufry2s 2013-08-05 07:37:40.753
mo3bbr454pfqv3qzq4zha045 2013-08-05 07:56:57.407
mosjjd454dh51uzr25tcbq55 2013-08-05 07:26:40.493
qwyurv55pv55ae55m0da45vi 2013-08-05 07:34:52.057
rdtx4d45y2zaw13cteyx2o55 2013-08-05 07:39:30.867
rsorttilqdfjux55driy4345 2013-08-05 07:42:31.060
s0nclxnl3fwexgzyzlgrf1yx 2013-08-05 07:27:15.523
u3lfht45zpw3ol55e0kwfi45 2013-08-05 10:32:58.003
zrnnypergmob1q55c0wfc3nu 2013-08-05 07:37:43.893

---

SQLCHANTer

Answer 4

Hi SQLCHANTer,

To cancel a job that is running on the report server, we can cancel the job directly or reduce the report execution time-out value in the SQL Server Management Studio. Please refer to the steps below:

1. Open SQL Server Management Studio, and connect to “Reporting Services”.
2. Under the Report Server node, right-click on the “Jobs” folder and select “Refresh”. Then, right-click on “Jobs” again and click “Cancel All Jobs”.
3. Right-click on the Report Server node and open the “Server Properties” dialog.
4. Click the “Execution” option, and set the “Limit report execution to the following number of seconds:” to a much smaller number. After this issue is resolved, this configuration should revert to the previous state. 

References:

• Managing a Running Process
• Setting Time-out Values for Report and Shared Dataset Processing

In addition, we can also use the KILL Transact-SQL statement to terminate a normal connection by terminating the transactions that are associated with the specified session ID. For the details information, you can refer to:
KILL (Transact-SQL)

Regards,
Mike Yin

If you have any feedback on our support, please click here

---

Mike Yin
TechNet Community Support

Answer 5

I do not see the Jobs folder as you have mentioned ... I only see Home , Security , shared Schedules

---

SQLCHANTer

Answer 6

Hi SQLCHANTer,

Thank for your posting.

From your description, you are using SQL Server 2005. In SSRS 2005, there is no Jobs folder when connecting to the Reporting Services instance through SQL Server Management Studio. So, you can cancel the Job from report manager (http://msdn.microsoft.com/en-us/library/ms189412(v=sql.90).aspx), or set Time-out Values for report and Shared Dataset processing (http://msdn.microsoft.com/en-us/library/ms155782(v=sql.90).aspx) as I mentioned above. Additionally, you can also use Kill T-SQL statement to do the trick.

Regards,
Mike Yin

If you have any feedback on our support, please click here

---

Mike Yin
TechNet Community Support