Overzicht open transacties in SQL Server

Stel je voor dat je op een SQL Server in een database tabel binnen transacties data aan het bewerken bent. Het is dan handig te weten of er nog open transacties zijn, die nog definitief moeten worden gemaakt of terug moeten worden gedraaid. Ook wanneer wordt geprobeerd om de transactielog vrij te maken, is het handig te weten of er nog transacties nodig zijn. Er zijn een aantal manieren om inzicht te krijgen in de transacties die nog open staan.

    DBCC OPENTRAN
    Deze functie wordt vooral gebruikt bij het vrijmaken van ruimte in de transactielogs. Het commando geeft de oudste transactie aan die nog open staat voor verwerking.
    Belangrijk: dit commando geeft alleen informatie over de actief geselecteerde database waarbinnen deze instructie wordt uitgevoerd.

    sys.sysprocesses
    De query …

    SELECT
    * 
    FROM sys.sysprocesses
    WHERE open_tran <> 0
    

    … geeft informatie over open transacties. Deze system view (dus geen tabel!) geeft onder andere informatie over het proces ID waaronder de transactie draait, op welke machine de transactie is gestart ook met welk account. Deze tabel is vooral handig om te identificeren wie, welke transacties open heeft.
    Belangrijk: de informatie wordt serverbreed weergegeven (en dus niet per databases)

    sys.dm_tran_active_transactions
    Onderstaande handige query is een query op de serverbrede view (dus geen tabel!) sys.dm_tran_active_transactions. Deze tabel wordt voornamelijk gebruikt om de status en voortgang van verschillende open transacties in kaart te brengen. De velden zijn in onderstaande query alvast vertaald naar betekenissen (in het Engels).

    select transaction_id, name, transaction_begin_time
     ,case transaction_type 
        when 1 then '1 = Read/write transaction'
        when 2 then '2 = Read-only transaction'
        when 3 then '3 = System transaction'
        when 4 then '4 = Distributed transaction'
    end as transaction_type 
    ,case transaction_state 
        when 0 then '0 = The transaction has not been completely initialized yet'
        when 1 then '1 = The transaction has been initialized but has not started'
        when 2 then '2 = The transaction is active'
        when 3 then '3 = The transaction has ended. This is used for read-only transactions'
        when 4 then '4 = The commit process has been initiated on the distributed transaction'
        when 5 then '5 = The transaction is in a prepared state and waiting resolution'
        when 6 then '6 = The transaction has been committed'
        when 7 then '7 = The transaction is being rolled back'
        when 8 then '8 = The transaction has been rolled back'
    end as transaction_state
    ,case dtc_state 
        when 1 then '1 = ACTIVE'
        when 2 then '2 = PREPARED'
        when 3 then '3 = COMMITTED'
        when 4 then '4 = ABORTED'
        when 5 then '5 = RECOVERED'
    end as dtc_state 
    ,transaction_status, transaction_status2,dtc_status, dtc_isolation_level, filestream_transaction_id
    from sys.dm_tran_active_transactions
    

    Belangrijk: de informatie wordt serverbreed weergegeven (en dus niet per databases)

    Credits
    Met dank aan: Tharif, Sebastian Brosch en Alisson Gomes
    Gebruikte link op StackOverflow: hier
    Microsoft Docs voor meer informatie: hier

Microsoft SQL Server vraagt om poort 587 (STARTTLS) bij database mail

Tijdens het installeren van Microsoft SQL Server op een dev omgeving, struikelde ik bij het installeren van database mail over de e-mail instellingen. Bij uitgaande mail gebruikte ik de reguliere SSL poort (465) die bij de meeste providers binnen een mailprogramma zoals MS Outlook ook wordt gebruikt. Bij Microsoft SQL Server werkt dient STARTTLS, poort 587 bij de meeste providers, te worden gebruikt. Vergeet bij het instellen dan niet het vinkje “This server requires a secure connection (SSL)” aan te zetten.

Hoe eenvoudig data tussen twee SQL Server databases wordt gekopieerd

Het komt wel eens voor dat er data tussen twee SQL Server databases moet worden gekopieerd. Daar zijn legio mogelijkheden voor. In deze bijdrage behandel ik er drie:

  • Via ‘Generate scripts…’
  • Via ‘Import/Export data…’
  • … en een combinatie van de twee bovenstaande opties

Take away:

  • In de import/export data wizard gebruikt u de data source Microsoft OLE DB Driver for SQL Server (Download)
  • De Microsoft OLE DB Provider for SQL Server en de SQL Server Native Client zijn vervallen; zie Microsoft Docs
  • De import/export wizard houdt geen rekening met de tabel opties in de brontabel(len)
  • De ‘Generate scripts…’ wizard is niet heel geschikt voor heel grote tabellen
  • Voor grote tabellen kunt u gebruik maken van ‘Generate scripts…’ voor het aanmaken van de objecten en de import/export wizard voor het kopiëren van de data