10

Suppose an application has established two connections to SQL Server.

  1. A session is actively running a query
  2. A session that is sleeping, waiting for the next instruction

Does killing this application via Task Manager kill both SQL connections?

0

3 Answers 3

24

I tested this with a .NET 6 console application using the Microsoft.Data.SqlClient driver, connecting to a remote network SQL Server instance. The connections were dropped immediately when the executable is killed via the Task Manager.

using Microsoft.Data.SqlClient;

var connectionString = "none of your business";

using (var connection1 = new SqlConnection(connectionString))
using (var connection2 = new SqlConnection(connectionString))
{
    connection1.Open();
    connection2.Open();
    Console.WriteLine("Connections opened, press enter to run a query on one of the connections");
    Console.ReadLine();

    using var command1 = new SqlCommand("SELECT * FROM master.dbo.spt_values v1 CROSS JOIN master.dbo.spt_values v2", connection1);
    var reader1 = command1.ExecuteReader();
    while (reader1.Read())
    {
        var number = reader1.GetInt32(1);
        Console.WriteLine($"Reader1 got number {number}");
    }
}

When this prompt shows:

Connections opened, press enter to run a query on one of the connections

I can run

EXEC master.dbo.sp_WhoIsActive 
    @show_sleeping_spids = 2, 
    @output_column_list = '[start_time][session_id][sql_text][cpu][wait_info][reads][status][program_name]',
    @filter = 'SQL Connection Test',               
    @filter_type = 'program';

and see the two connections:

screenshot of sp_WhoIsActive results showing the two idle connections

Now, if I press enter, a query is run on one connection, and I can run that same sp_WhoIsActive query:

screenshot of sp_WhoIsActive results show two connections, one with a runnable query accumulating CPU and reads

I then killed SqlConnectionTest.exe from the details tab of the Task Manager:

screenshot of the "are you sure" dialog in Task Manager when I chose to kill the program

I soon as I clicked "End Process," I re-ran the sp_WhoIsActive query and both connections (sleeping and active) were gone (so, within 1-2 seconds):

screenshot of sp_WhoIsActive results show no rows returned

Note: the behavior of this test is the same whether the using blocks are there or not.


It's worth pointing out that this test is an example based on one framework and one driver. This behavior might differ between code frameworks, drivers, operating systems, etc.


If your application is running queries that do strange things that go "outside of SQL Server" the session endures in SQL Server (thanks AMtwo). For example, if I change the example program above to run this query instead:

xp_cmdshell 'ping 127.0.0.1 -n 30 > nul';

Even if I kill the application, that session stays open for 30 seconds (when the external call to cmd completes).

1
  • 3
    It is worth noting that pulling the network cable, cutting power or bluescreening will not kill a TCP connection. SQL Server will not immediately notice that its peer has gone away.
    – usr
    Sep 23 at 15:39
17

Assuming you are using a complete task-kill: It basically depends on whether SQL Server gets any notification about the connection being dropped. If the operating system sends a TCP FIN or RST packet then presumably SQL Server will immediately kill the connection. Anecdotal testing on Windows 10 shows that the OS sends a RST packet automatically.

Otherwise, it will not necessarily kill the connection immediately because as far as the server is concerned, there is just no response by the client, similar to what would happen if the network connection failed.

Eventually the server times out the connection if the client has not responded within a certain amount of time.

Note that this only refers to the connection, the session is normally killed as well and cannot be resumed even if the client wakes up. A new session would be necessary. But in certain situations, the session can stay around, even though there is no connection associated with it, such as when it is being rolled back or when an external cmd command is being issued.

2
  • 3
    one cannot kill a process without resetting all its tcp connections in any modern OS that has the notion of process
    – fraxinus
    Sep 23 at 6:18
  • 5
    But pulling the power or network cable, or shutting off a VM, would not send a RST, which was the point I was trying to make Sep 23 at 9:09
-2

Any client-server database system (which your sql-server is) MUST deal with loss of connection to the client. In general, it will be as if the client gracefully closed when the server next tries to send to or receive from the client. This means:

  • A long running query not yet returning results might continue running even if the client is killed.
  • A long running data modification might finish, and if appropriate be autocommited before the client disconnection is detected.

What isn't so certain is dealing with the client ceasing to respond at all. A good server will have a "keep-alive" mechanism to test every so often that the client is still there. Again, it might not apply during a long-running operation. This kind of thing may happen if the client's computer is powered off, or rebooted; and the symptoms seen be the keep-alive differ.

Non-client-server database systems deal with this the same way a client-server system deals with the server crashing or being killed: with a multi-stage commit system.

New contributor
David G. is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.