Highlighted
PM Thompson Outstanding Contributor.
Outstanding Contributor.
8793 views

How to terminate a report query if the report is cancelled

I was recently working on a report against several primary tables that have about 1.8 million records. Several iterations of the report would take many minutes to return. That wouldn't work for our clients so I would hit F5 (browser refresh) to cancel the report. After several times doing this the server started to noticibly slow down. The Windows app event log shows what I consider the typical "request active since..." message.

7327123,Application,Serena Business Mashups Application Engine,FAILURE AUDIT,xxxx.yyy.com,4/29/2016 12:28:37 PM,3,None,"Message text not available. Insertion strings: Audit Failure occurred in file: 'CacheMgr.cpp', line 552. The following request from user 'pthompson' at address 'aa.bb.cc.dd' has been active since 04/29/2016 10:38:14: Thread number 15, 4 active, 20 total Request: 'ReportPage&Template=reports%%2Flist&projectid=4&querytype=2&reportid=32000&reporttype=25&tableid=1016' Db Query Start time Not Set, End Time Not Set Query: '' Flist&projectid=4&querytype=2&reportid=32000&reporttype=25&tableid=1016' Db Query Start time Not Set, End Time Not Set Query: '' "

Is the db query still going on and if so is there any way to effectively kill the query without restarting SQL server or IIS? It is, after all, the production server.
Tags (2)
0 Likes
2 Replies
jmalin Absent Member.
Absent Member.

Re: How to terminate a report query if the report is cancelled

View running SQL processes using the following code. Issue a "Kill" command once you're sure which is the culprit.

A "Cancel" button on reports would be a great Idea if not already submitted:


SELECT s.session_id
,r.STATUS
,r.blocking_session_id 'blocked by'
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'Wait Time (in Sec)'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
lmattie Absent Member.
Absent Member.

Re: How to terminate a report query if the report is cancelled

Unless you are seeing a performance degradation, I would just let it run. Killing the SQL connection might mess up the next thread that is trying to use the db connection pool. Both SQL and SBM have other threads to process additional requests. If you decide it needs to be stopped, restarting IIS would be sufficient.

For speeding up your report query, make sure you include fields that are indexed or add new indexes on key fields of your query to ensure your query does not have to search the entire set of records to obtain the results.

Note that there is a trade off, since adding additional indexes on a database table will slow down submits into that table.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.