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

Correct instellen van SQL Server tempdb (best-practice)

Nog zeer regelmatig kom ik SQL Servers tegen, waarbij de tempdb niet optimaal is ingericht. Af en toe bekruipt mij het gevoel dat de tempdb database het sluitstuk van een SQL Server installatie is. Aan die database administrators geef ik graag mee dat tempdb een essentiële bouwsteen van de SQL Server is, die de performance van SQL Server kunnen maken of breken.

Waar tempdb het meest voor wordt gebruikt

  • frequent maken en verwijderen van tijdelijke tabellen
  • tijdelijke objecten die bij CURSORS worden gebruikt
  • tijdelijke objecten die in combinatie met ORDER BY worden gebruikt
  • tijdelijke objecten die in combinatie met GROUP BY worden gebruikt
  • tijdelijke objecten die verband houden met het samenvoegen van data uit meerdere tabellen (HASH PLANS)

Bovenstaande acties kunnen tot opstoppingen in de tempdb leiden en daarmee de doorlooptijd van query’s.

Hoe wordt tempdb het beste ingericht

  • Verhoog het aantal data bestanden. Voor machines met minder dan 8 processoren: houd het aantal databestanden gelijk aan het aantal processoren; bij meer dan 8 processoren, gebruik om te beginnen 8 data bestanden. Mocht dat niet voldoende zijn om opstopping tegen te gaan, verhoog dan het aantal data bestanden met een veelvoud van vier totdat het probleem met opstoppingen is opgelost óf het aantal data bestanden gelijk is aan het aantal processoren. Het advies is, zeker wanneer er meer dan acht data bestanden voor tempdb nodig blijken, de opzet van de gebruikte query’s nog een keer na te lopen.
  • Zorg ervoor dat de databestanden van de tempdb altijd even groot zijn.
  • Het is beter om meerdere kleinere databestanden in de tempdb te hebben, dan één grote
  • Tot SQL Server 2014 kan het aanzetten van trace flag 1118 een significante verbetering in de performance betekeken. Trace flag 1118 verlicht de druk op de zogenoemde (S)GAM pagina’s, omdat alle enkelvoudige pagina toewijzingen worden uitgeschakeld. Dit gedrag is standaard vanaf SQL Server 2016; trace flag 1118 hoeft op deze versie en hoger niet te worden aangezet.
  • Zet de databestanden en logbestanden van de tempdb bij voorkeur op een aparte fysieke schijf; niet zijnde de C-schijf
  • Zorg voor een ruime autogrowth (in plaats van de standaard 64Mb die Microsoft gebruikt)
  • Monitor de groei van de tempdb

Gebruikte bronnen vanuit Microsoft:
Microsoft Docs: tempdb database
Support Microsoft: Recommendation to reduce allocation contention
Micorsoft Docs: Working with tempdb in SQL Server 2005

Foutmelding in kubusverwerking via een SQL Agent Job

UItgangspunt
Er is een kubusdatabase waarbij meetwaarden worden gevonden die een onbekende dimensiewaarde hebben. Om te voorkomen dat dit een foutmelding tijdens het bijwerken van de data (process database) plaatsvindt, worden in het tabblad “Dimension key errors” binnen Batch Settings Summary gewijzigd. Daarbij worden de volgende keuzes gemaakt:

– Bij fouten in dimensies worde de waarde naar ‘unknown’ geconverteerd
– Er worden in totaal maximaal 500 dimensiefouten geaccepteerd
– Wanneer de dimensiewaardie niet wordt gevonden, gaat het bijwerken van de kubus door
– Wanneer een niet toegestane Null key wordt gevonden, gaat het bijwerken ook door

Grafisch:

Wanneer de kubus op deze wijze via een SQL Agent job wordt bijgewerkt (process database) zal de SQL Agent job met de volgende melding falen:

<Warning WarningCode=”1092354050″ Description=”Server: Operation completed with … problems logged.” … />

De SQL Agent job geeft dus een harde foutmelding. Wees er dus op bedacht dat het aantal en soort fouten bij het instellen van de kubusverversing zijn geaccepteerd en dat de SQL Agent job daarmee eigenlijk onterecht faalt.

Omdat de SQL Agent een onterechte fout geeft, is er een stukje maatwerkcode nodig om de jobs periodiek op juistheid en volledigheid te controleren. Onderstaand T-SQL kan hiervoor worden gebruikt. U zult zelf even moeten kijken welke velden uit deze tabellen bruikbaar voor u zijn.

SELECT
*
FROM msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id AND s.step_id = h.step_id

 

SQL Server 2019 bug in logshipping

Bij het opzetten van logshipomgevingen in SQL Server 2019 werd mijn aandacht gevraagd voor een vrij bijzondere foutmelding in de geschiedenis van de LSBackup, LSCopy en LSRestore jobs. De foutmelding was:

DateTime  *** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data)
DateTime  *** Error: Object must implement IConvertible.(mscorlib) ***

Dit is een bekende bug bij Microsoft die in Cumulative Update 2 is opgelost.

Probleem is beschreven in deze link: https://support.microsoft.com/en-us/help/4537869/fix-log-shipping-agent-is-not-able-to-log-history-and-error-informatio

Cumulative update 2 voor SQL Server 2019 is op 13 februari 2020 via deze kaartbak item gereleased: https://support.microsoft.com/en-us/help/4536075/cumulative-update-2-for-sql-server-2019

SQL Server foutmelding “String or binary data would be truncated” met trace flag 460 analyseren

Wellicht dat u de foutmelding herkent. Met deze foutmelding begint de eindeloze zoektocht naar het juiste veld en record waar dit probleem zich in voordoet.

Eindelijk heeft Microsoft een oplossing hiervoor gemaakt, te weten trace flag 460. Wanneer deze trace flag wordt aangezet, geeft SQL Server meer informatie over de foutmelding, namelijk het veld waar het probleem zich voordoet en het gevonden record.

Ik verwacht dat ontwikkelaars hier de nodige tijd mee zullen besparen. Deze trace flag is beschikbaar in SQL Server 2016 SP2, CU6 en SQL Server 2017, CU12

Meer informatie in deze link van Microsoft: https://support.microsoft.com/en-us/help/4468101/optional-replacement-for-string-or-binary-data-would-be-truncated. Voorbeelden in het Engels zijn beschikbaar via Brent Ozar: https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/.

SQL Server Database in Recovery Pending

Vandaag vond ik één van mijn SQL Server testdatabases in status recovery pending. In deze bijdrage een korte weergave van mijn analyse, bevindingen en acties

Wanneer zich een incident op de SQL Server niveau voordoet, is het altijd een goed idee om in de SQL Server logfile te kijken, wat er is gebeurd, wanneer dat is gebeurd en onder welke omstandigheden iets is gebeurd.

Zo vond ik dat de foutmelding op deze database zich voordeed onder de omstandigheid dat de machine waarop SQL Server staat geïnstalleerd een herstart heeft gehad. SQL Server vermeldde in de log dat de betrokken database daarna niet beschikbaar was, omdat de database bestanden niet gevonden werden. Deze database bestanden staan voor deze testdatabase op een NAS, waar de machine op het moment van starten van SQL Server nog geen toegang toe had. Dan wordt dus duidelijk waar de foutmelding vandaan komt.

Ik heb vastgesteld dat de NAS wel weer beschikbaar was op de wijze zoals ik die had geconfigureerd. Ik heb de SQL Server services opnieuw gestart en het probleem was daarmee verholpen.

Lessons learnt: kijk bij incidenten altijd in de SQL Server logs.

T-SQL – SELECT TOP 100 vs SELECT TOP 101

SQL Server heeft een fijne manier van sorteren. Een SELECT TOP 100 wordt door SQL Server veel efficiënter gesorteerd dan een SELECT TOP 101+. In de StackOverflow database wordt dat duidelijk, wanneer de ‘Actual Query Plan’ wordt aangezet en onderstaande query’s worden uitgevoerd

SELECT TOP 100
*
FROM dbo.Users
ORDER BY Reputation DESC
GO

SELECT TOP 101
*
FROM dbo.Users
ORDER BY Reputation DESC
GO

Het verschil in aanpak door SQL Server wordt duidelijk door te kijken naar de memory grant.

Uw MS SQL Server database op een NAS?

Wellicht dat u dit herkent: u heeft een ontwikkeldatabase waar op uw servers geen ruimte meer voor is, maar u heeft nog wel een NAS staan met een hoop opslag beschikbaar. Het probleem: uw Microsoft SQL Server herkent de UNC adressen van de NAS niet en kan er niet goed mee om gaan. De oplossing: gebruik iSCSI. iSCSI zorgt ervoor dat (een deel van) uw NAS direct aan uw computer kan worden gekoppeld, daar een drive letter krijgt en door uw computer kan worden beheerd/benaderd. SQLTeam.NL beschikt over een Synology DS918+. Op de website van Synology wordt uitgelegd hoe op een Synology NAS een iSCSI Target-service kan worden aangemaakt en hoe deze op uw computer bekend wordt gemaakt.

  • Aanmaken van iSCSI Target-service op een Synology
  • Koppelen van de service aan uw computer
  • Het spreekt voor zich dat deze oplossing alleen voor ontwikkel- en testdatabases is bedoeld. Het draaien van database oplossingen op een NAS is nu eenmaal langzamer.