DW & Link Server force query timoeout

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

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

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?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources