O que acontece com uma transação aberta quando a janela da sessão é fechada?

Supondo que quando você diz " janela de sessão " está se referindo a uma janela de consulta no SSMS. No SSMS, cada janela de consulta geralmente é sua própria conexão. Quando você fecha a janela, teoricamente fecha a conexão. (Teoricamente porque depende se a conexão é usada no pool de conexões ou não.)

Quando a conexão entre o cliente e o SQL Server é interrompida no meio de uma transação aberta, essa transação será revertida automaticamente. Isso é observado na resposta de Hannah em Quais são todas as maneiras pelas quais uma transação aberta será revertida? : " Desconexão pelo cliente, ou servidor, ou qualquer parte da infraestrutura entre ".

Respondendo suas perguntas

O que teria acontecido se eu tivesse fechado a janela que tinha a transação aberta sem revertê-la?

Na premissa de que a maioria dos RDBMS funciona com base no princípio ACID, sua transação seria revertida.

_{( ênfase minha)}

Em ciência da computação, ACID (atomicidade, consistência, isolamento, durabilidade) é um conjunto de propriedades de transações de banco de dados destinadas a garantir a validade dos dados apesar de erros, falhas de energia e outros contratempos. [1]No contexto de bancos de dados, uma sequência de operações de banco de dados que satisfaça as propriedades ACID (que podem ser percebidas como uma única operação lógica nos dados) é chamada de transação. Por exemplo, uma transferência de fundos de uma conta bancária para outra, mesmo envolvendo várias alterações, como debitar uma conta e creditar outra, é uma única transação.

^{Referência: ACID (Wikipédia)}

Fechar uma janela deve resultar na reversão da transação para garantir as propriedades ACID das transações do banco de dados.

Fechando a janela de consulta no SSMS (caso real)

No SSMS, você é apresentado a uma caixa de diálogo que permite decidir como reagir ao fechar uma janela:

Fechamento forçado do SSMS via Taskmanager (caso real)

Quando o SSMS é encerrado por meio do Gerenciador de tarefas e/ou fechado à força, a transação é revertida automaticamente.

Isso pode ser reproduzido com os seguintes passos:

1. Crie a seguinte tabela em um banco de dados

   USE [EverGrowingDatabase]
   GO
   
   /****** Object:  Table [dbo].[EverGrowingTable]    Script Date: 21.10.2022 15:15:28 ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[EverGrowingTable](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [shorttext] [nchar](20) NOT NULL,
       [longtext] [nchar](1000) NULL
       ) ON [PRIMARY]
   GO
   

2. Abra um SSMS e execute o seguinte script:

   USE [EverGrowingDatabase]
   go
   BEGIN TRAN
   INSERT INTO EverGrowingTable
   (
       -- id -- this column value is auto-generated
       shorttext,
       longtext
   )
   VALUES
   (
       'SomeRealyShortText',
       REPLICATE('SomeRealyLongText',50)
   
   )
   --ROLLBACK TRANSACTION
   

3. Abra um segundo SSMS e execute o seguinte script:

   USE [EverGrowingDatabase]
   GO
   SELECT * FROM EverGrowingTable AS egt WHERE egt.shorttext = 'SomeRealyShortText';
   

4. Mate o primeiro SSMS via Gerenciador de Tarefas.

5. Verifique se o segundo script da etapa 3. não exibe nenhum dado.

... e como mencionado por JD em sua resposta vinculada.

A transação estaria aberta até que alguém a revertesse manualmente ou o servidor SQL reverte quaisquer transações não confirmadas quando uma janela é fechada?

A transação seria revertida em circunstâncias normais.

Além disso, como descubro todas as transações abertas em um banco de dados em qualquer sessão?

Para determinar as transações atualmente abertas, você pode consultar o sys.dm_exec_sessions, sys.dm_exec_connectionse sys.dm_exec_reuqestsoutras exibições de gerenciamento do sistema para determinar quaisquer transações abertas em uma solicitação ou em uma sessão.

Roteiro de exemplo:

SELECT      des1.session_id             AS Session_ID_S,
            sdb.name                    AS DatabaseName,
            ssp.name                    AS SQL_Login_Name,
            des1.nt_user_name           AS NT_User_Name,  
            dowt.wait_type              AS Wait_Type,
            dowt.blocking_session_id    AS Blocking_Session_ID,
            dowt.resource_description   AS Ressource_Description,
            der.[status]                AS Request_Status,
            der.wait_type               AS Request_WaitType,
            der.open_transaction_count  AS Request_Open_Transactions,
            des1.open_transaction_count AS Session_Open_Transactions,
            des1.host_name              AS HostName,
            des1.host_process_id        AS HostProcessID,
            des1.program_name           AS Program_Name,
            dest.[text]                 AS SQL_Text,
            deqp.query_plan             AS Query_Plan,
            CASE  WHEN dowt.blocking_session_id  IS NOT NULL AND dowt.blocking_session_id != des1.session_id THEN '--kill ' + cast(dowt.blocking_session_id AS nvarchar(20)) ELSE ' ' END AS killcommand,
            'EOR'                       AS EOR
FROM        sys.dm_exec_sessions        AS des1
            LEFT 
            JOIN sys.dm_exec_connections    AS dec1    
                ON  des1.session_id         = dec1.session_id
            LEFT -- comment out LEFT to display only sessions that have gone parallel
            JOIN sys.dm_os_waiting_tasks    AS dowt 
                ON  des1.session_id         = dowt.session_id
            LEFT -- comment out LEFT to display only sessions currently executing statements
            JOIN sys.dm_exec_requests       AS der    
                ON  des1.session_id         = der.session_id
            LEFT -- comment out LEFT to ...... (I'm not telling)
            JOIN sys.server_principals      AS ssp  
                ON  des1.login_name         = ssp.name
            /* ==================== This is for SQL Server 2012 + ===================*/
            LEFT 
            JOIN sys.databases              AS sdb
                ON des1.database_id         = sdb.database_id
             /* ==================== This is for SQL Server 2012 + ===================*/
            
            /* ==================== This is for SQL Server 2008 R2 ===================
            LEFT
            JOIN sys.sysprocesses as ss
                ON ss.spid = des1.session_id
            LEFT 
            JOIN sys.databases as sdb
                ON sdb.database_id = ss.dbid
             ==================== This is for SQL Server 2008 R2 ===================*/
            
            OUTER APPLY sys.dm_exec_sql_text(der.sql_handle)     AS dest -- Retrieve Actual SQL Text
            OUTER APPLY sys.dm_exec_query_plan(der.plan_handle)  AS deqp -- Retrieve Query Plan (XML)
WHERE       1=1
    -- AND      sdb.name in ('XPDATA', 'XPVDIR')
    AND        des1.is_user_process     = 1

As colunas Session_Open_Tranactionse Request_Open_Transactionmostrariam as transações ainda em execução para qualquer dado Session_ID.