3

Wihin my C# code I am using the CommandTimeout function to ensure that any query that executes longer than 30s is terminated both from the server and database. However when listing the currently running queries on the database the query that was set to cancel after 30s runs well beyond 30s 

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand sqlCommand = new SqlCommand(query, connection);

    //Set Timeout to 30s
    sqlCommand.CommandTimeout = 30;
    SqlDataAdapter da = new SqlDataAdapter(sqlCommand);

    da.Fill(response);
    connection.Close();
    da.Dispose();
}

enter image description here

Why is the query still running in the DB? Is my only option right now is to send another query from the server to kill the query (KILL [session_id]) after 30s?

EDIT: 300Mb of data is being returned for this query.

Improve this question
8
  • 9
    Command timeout is a client side setting. Meaning that is how long the client will wait. The sql engine doesn't care, it will keep running and running even if the client has long abandoned the thread. Commented Aug 1, 2018 at 15:31
  • 2
    Possible duplicate of What Happens To a Query If It Times Out? Commented Aug 1, 2018 at 15:32
  • 1
    Half of you guys are telling me it kills in db side the other half client side Commented Aug 1, 2018 at 15:33
  • 3
    Your C# will signal to the DB that it doesn't want to wait anymore. The SQL box will stop that query when it thinks it can. This may be instantly, it may not depending on what the SQL box is doing what your query is, etc. I'd be very careful on just killing it. If SQL doesn't want to cancel that query it likely has a very good reason. Commented Aug 1, 2018 at 15:34
  • 1
    Unrelated tips: SqlCommand and SqlDataAdapter are both IDisposable so each should be in a using block. This will make the da.Dispose() redundant, and the connection.Close() already is redundant, since exiting the connection's using block will call Dispose, which calls Close. Commented Aug 1, 2018 at 19:20

1 Answer 1

Reset to default
7

There are a number of posts on StackOverflow indicating that SqlCommand.CommandTimeout won't affect the behavior of SqlDataAdapter.Fill. Instead, you supposedly have to set the SqlDataAdapter's SelectCommand.CommandTimeout property.

However, there are other posts which seem to indicate that even this doesn't work. This one in particular makes me think that the query will only be canceled if the timeout occurs before the query starts yielding results. Once results start coming in, it appears to ignore all timeouts.

My recommendation would be to reconsider using SqlDataAdapter. Depending on your use case, maybe a library like Dapper would work better for you?

You may also want to consider reporting this as a defect to the .NET team. I've had mixed success in the past reporting such errors; it depends on whether the team wants to prioritize fixing the issue.

Update

It looks like this may be the intended, documented behavior, as Marc Gravell points out here.

lol: from the documentation (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx)

For example, with a 30 second time out, if Read requires two network packets, then it has 30 seconds to read both network packets. If you call Read again, it will have another 30 seconds to read any data that it requires.

So: this timeout resets itself every Read. So: the only way it'll trip is if any single Read operation takes longer than 2s. As long as the SQL Server manages to get at least one row onto the pipe in that time: it won't timeout via either API.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the reply. Setting the SelectCommand.CommandTimeout does not change anything. I feel you are right that the timeout is being ignored because the results are being filled in by the adapter continuously. When running the query in SQLSMS the rows are returned one by one and not all at once. I'm not sure if Dapper will make a difference
@BDillan: Looks like you're right: Dapper probably won't make a difference.
@BDillan: However, it does look like Dapper respects a cancellation token... sort of. if you use a cancellation token on a dapper CommandDefinition, and pass it to a QueryAsync method, Dapper will attempt to cancel the query after a specified period of time, even if it's pulling data from the server at that time. The weird thing is that dapper's attempt to cancel the query appears to be subject to the command timeout that you specify. So if the query starts returning values right away, you'll get a SqlException (Timeout Expired) after commandTimeout + cancellationToken.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.