sjeniferal (4/12/2010)
So it will execute this forever? But obviously it won't execute forever, correct? Is there a time limit within SQL Server after which long running queries will be killed? How does that work? I mean some queries will take a long time to run and some others like the one in this example are faulty and will run forever....So, in this case the app will get a SQLTimeout error? I guess my question is what is the sequence of events internally inside SQL Server and between SQL Server and (a .NET) app that finally result in a SQLTimeout error?
Unless some client decides that the query has run long enough, it will continue forever. Or at least until the next server reboot.
If a client (or a human) decides that the query has run long enough, an "Attention" signal will be sent to the server or a DBA will kill the process. That will cause a rollback (which will be pretty fast in this case, since no modifications are made) and a message to be sent to the client.