Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 DW & Link Server force query timoeout

Author  Topic 

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2014-10-15 : 09:45:30
Good day,

Please help, the problem I have is that users execute long running queries (20min) against prod server & Linked server

now on linked server I change the setting in Connections - Query TimeOut = 600 (10min) and the Prod Server: Allow Remote Connections to the server - remote query timeout: 600 (10min)

the opensourcequeries & SQL queries runs for 20min, results are displayed immediately but completes in 20min

I wanted to script to control queries executions but these settings don't do the trick

Please assist

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-17 : 08:16:44
are the tables used in the queries indexed?

Javeed Ahmed
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2014-10-17 : 09:50:06
Good day, Thank you for assisting.

I want to prevent & stop queries executed by all developers/users (SSRS using Linked Server) on DW Prod server, it is not queries in BI control - like other teams uses the linked servers for ssrs & some of juniors running queries directly on prod that is really in-efficient that I don't see or know of then its too late as it kills the server

Want anything that runs longer than 10min to stop/terminated/kill

Correct results are returned but query exceeds the 10min cut-off, how do I prevent & control queries running longer the 10min - whether its Linked Server/Prod Server Even when results are being returned but still running?

the query governor also does not do the trick

Please Help
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2014-10-19 : 06:58:12
Is there a way in SQL to calculate all queries runtime on server then based on condition kill session - then insert this into a job agent step, if so the script example

please any ideas?
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2014-10-19 : 12:19:37
Thank You, I was working on the below - Added to an agent job that loops through the query & sets an elapsed_time target then kill session

SELECT


sqltext.TEXT,req.session_id,req.status,req.command,req.cpu_time,req.total_elapsed_time,s.login_name,db.name


FROM


sys.dm_exec_requests req


CROSS


APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext


INNER


JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id


inner


JOIN sys.sysdatabases AS db ON req.database_id = db.dbid

WHERE


req.status in ('running','suspended','runnable')


and


req.session_id != @@SPID


AND


req.session_id > 50

AND

sqltext.dbid IS NULL



--KILL 202
Go to Top of Page
   

- Advertisement -