0

I’m a self-taught DBA in a manufacturing environment full of engineers who like to fiddle. Although I have not given them sysadmin or any explicit extra rights I have an issue with some engineers running ‘killer’ queries, some very io intensive. This would not be an issue normally given the default Command Timeout of 30 seconds for connections, but some of these engineers have purchased SQL Developer, purely for the autocomplete feature, and of course this by default connects them with an infinite ‘CommandTimeout’.

What I would like to do, in some way, is to alter either/or new connections/sessions and set their CommandTimeout to something more reasonable, maybe less than 30 seconds. For new connections, I envision its some sort of Database Trigger that then executes a command under their context? Existing connections, maybe something horrible in the system tables?

Colin Dumitru
  • 3,327
  • 6
  • 30
  • 47
Steve Smith
  • 1
  • 1

1 Answers1

1

Unfortunately you cannot override the client CommandTimeout value. As you might have noticed it is a client configuration and not an instance related configuration.

However you can use the Query (Resource) Governor to avoid long running queries.

I suggest a further reading on this topics:

http://msdn.microsoft.com/en-us/library/bb933866.aspx

http://technet.microsoft.com/en-us/library/ms191219.aspx

Sorry not to provide a complete answer but I think that this will help you.

gustavodidomenico
  • 4,640
  • 1
  • 34
  • 50
  • Firstly, thanks for you comment. Much appreciated. I have started looking into your suggestions on Resource Governing. So far, as still playing, I have found that it merely extends thier query times due to the reduced resource allocation. The queries still run, just take longer... It also does not inform then as to why it is taking an extended amount of time. I have not yet figured out how to end the query at a set execution time. – Steve Smith Feb 20 '13 at 13:39
  • I have also looked at: 1...Server LOGON Trigger : Does not work as at time of execution the session does not yet exist. 2...Database Trigger : No LOGON or SELECT attribute. 3...EXECUTE AS : Will not attach and alter an existing session. – Steve Smith Feb 20 '13 at 13:40
  • I have also found out by looking at sys.dm_exec_requests.total_elapsed_time I can see how long a query has been executing. This being the case I have a script I can run which will 'KILL' the offending SPID after a certain time. Sounds ok, but it kills thier session, so its whole relient on the client software being able handle this and reconnect after, but still they have no information as to why the session died. Perhaps there is a way to abort a query rather than KILL'ing the session? – Steve Smith Feb 20 '13 at 13:40