What Happens To a Query If It Times Out?

Question

Let's say I have a query that is sent to my SQL-Server database, it takes more than 30 seconds, and my program throws an SQL Query Timeout exception. Is the query still chugging along on my database or does it get terminated as soon as the exception is thrown?

Answer 1

A client signals a query timeout to the server using an attention event. An attention event is simply a distinct type of TDS packet a SQL Server client can send to it. In addition to connect/disconnect, T-SQL batch, and RPC events, a client can signal an attention to the server. An attention tells the server to cancel the connection's currently executing query (if there is one) as soon as possible. An attention doesn't rollback open transactions, and it doesn't stop the currently executing query on a dime -- the server aborts whatever it was doing for the connection at the next available opportunity. Usually, this happens pretty quickly, but not always.

Source There's no such thing as a query timeout...

Answer 2

When the client decides that the command has run long enough, it issues an "Abort". The query simply stops running in the database.

Any CATCH block won't be hit, transactions will be left open and locks can still remain allocated after this, even if the connection is closed because "close" means "return to connection pool".

If you expect a lot of Command Timeouts then consider using SET XACT_ABORT ON (and this too) that will release locks and rollback transactions. or fix the code...