Does killing an application kill the SQL connection?

Suppose an application has established two connections to SQL Server.

• A session is actively running a query

• A session that is sleeping, waiting for the next instruction

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

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:

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

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

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):

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).

StackExchange Database Administrators Q#317237, answer score: 24