De grootte van het transactielogboekbestand beheren

Van toepassing op:SQL Server

In dit artikel wordt beschreven hoe u de grootte van het transactielogboek van SQL Server bewaakt, het transactielogboek verkleint, een transactielogboekbestand toevoegt of vergroot, de groeisnelheid van het tempdb transactielogboek optimaliseert en de groei van een transactielogboekbestand bepaalt.

Dit artikel is van toepassing op SQL Server. Hoewel het proces vergelijkbaar is, raadpleegt u voor bestandsruimtebeheer in Azure SQL:

Inzicht in typen opslagruimte voor een database

Inzicht in de volgende opslagruimten is belangrijk voor het beheren van de bestandsruimte van een database.

Databasehoeveelheid Definition Comments
Gebruikte gegevensruimte De ruimte die wordt gebruikt voor het opslaan van databasegegevens. Over het algemeen neemt de gebruikte ruimte toe bij invoegingen en af bij verwijderingen. In sommige gevallen verandert de gebruikte ruimte niet bij invoegingen of verwijderingen, afhankelijk van de hoeveelheid en het patroon van gegevens die betrokken zijn bij de bewerking en eventuele fragmentatie. Als u bijvoorbeeld één rij van elke gegevenspagina verwijdert, neemt de gebruikte ruimte niet noodzakelijkerwijs af.
Toegewezen gegevensruimte De opgemaakte bestandsruimte die beschikbaar is gemaakt voor het opslaan van databasegegevens. De hoeveelheid toegewezen ruimte wordt automatisch groter, maar neemt nooit af na verwijderingen. Dit gedrag zorgt ervoor dat toekomstige invoegingen sneller zijn omdat ruimte niet opnieuw hoeft te worden opgemaakt.
Toegewezen gegevensruimte, maar niet gebruikt Het verschil tussen de toegewezen hoeveelheid en de gebruikte gegevensruimte. Deze hoeveelheid geeft de maximale vrije ruimte aan die door het verkleinen van de gegevensbestanden van de database kan worden vrijgemaakt.
Maximale grootte van gegevens De maximale hoeveelheid ruimte voor het opslaan van databasegegevens. De hoeveelheid toegewezen gegevensruimte kan niet groter worden dan de maximale grootte van de gegevens.

In het volgende diagram ziet u de relaties tussen de verschillende typen opslagruimte voor een database.

Diagram met de relaties tussen de verschillende typen opslagruimte voor een database.

Een query uitvoeren op één database voor informatie over de bestandsruimte

Gebruik de volgende query om de hoeveelheid toegewezen databasebestandsruimte en de hoeveelheid ongebruikte ruimte te retourneren. Eenheden van het queryresultaat zijn in MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Gebruik van logboekruimte bewaken

Bewaak het gebruik van logboekruimte met behulp van sys.dm_db_log_space_usage. Deze DMV retourneert informatie over de hoeveelheid gebruikte logboekruimte en geeft aan wanneer het transactielogboek moet worden afgekapt.

Voor informatie over de huidige logboekbestandsgrootte, de maximale grootte en de optie voor automatisch vergroten van het bestand, kunt u ook het size, max_sizeen growth de kolommen voor dat logboekbestand in sys.database_files gebruiken.

Belangrijk

Vermijd overbelasting van de logboekschijf. Zorg ervoor dat de logboekopslag bestand is tegen de IOPS - en lage latentievereisten voor uw transactionele belasting.

Een logboekbestand verkleinen

Verklein het logboekbestand om de fysieke grootte te verkleinen door vrije ruimte naar het besturingssysteem te retourneren. Een verkleining maakt alleen een verschil wanneer een transactielogboekbestand ongebruikte ruimte bevat.

Als het logboekbestand vol is, waarschijnlijk vanwege geopende transacties, onderzoekt u wat verhindert dat het transactielogboek wordt afgekapt.

Waarschuwing

Verkleiningsbewerkingen moeten niet worden beschouwd als een normale onderhoudsbewerking. Voor gegevens en logboekbestanden die groeien vanwege regelmatige terugkerende zakelijke activiteiten, zijn geen verkleiningsbewerkingen vereist. Verkleincommando's beïnvloeden de prestaties van de database tijdens het uitvoeren. Ze moeten worden uitgevoerd tijdens perioden met een laag gebruik. Het is niet raadzaam om gegevensbestanden te verkleinen als een normale toepassingsworkload ervoor zorgt dat de bestanden weer dezelfde toegewezen grootte krijgen.

Houd rekening met de mogelijke negatieve invloed op de prestaties van het verkleinen van databasebestanden. Zie Indexonderhoud na het verkleinen.

Voordat u het transactielogboek verkleint, moet u rekening houden met factoren die het afkappen van logboeken kunnen vertragen. Als er weer opslagruimte nodig is nadat een logboek is verkleind, neemt het transactielogboek opnieuw toe, wat leidt tot prestatie-overhead tijdens logboekgroeibewerkingen. Zie Aanbevelingen voor meer informatie.

U kunt een logboekbestand alleen verkleinen terwijl de database online is en ten minste één virtueel logboekbestand (VLF) gratis is. In sommige gevallen is het verkleinen van het logboek mogelijk alleen mogelijk na de volgende afkapping van het logboek.

Sommige factoren, zoals een langlopende transactie, kunnen VLF's gedurende een langere periode actief houden, de verkleining van logboeken beperken of zelfs voorkomen dat het logboek helemaal afneemt. Zie Factoren die het afkappen van logboeken kunnen vertragenvoor meer informatie.

Als u een logboekbestand verkleint, worden een of meer VLF's verwijderd die geen deel uitmaken van het logische logboek (dat wil gezegd inactieve VLF's). Wanneer u een transactielogboekbestand verkleint, worden inactieve VLF's verwijderd aan het einde van het logboekbestand om het logboek te verminderen tot ongeveer de doelgrootte.

Raadpleeg de volgende bronnen voor meer informatie over verkleiningsbewerkingen:

Een logboekbestand verkleinen (zonder databasebestanden te verkleinen)

Verkleiningsgebeurtenissen van logbestanden bewaken

Logboekruimte bewaken

Indexonderhoud na een verkleiningsbewerking

Indexen kunnen worden gefragmenteerd nadat een verkleiningsbewerking is voltooid voor gegevensbestanden. Deze fragmentatie vermindert de effectiviteit voor prestatieoptimalisatie voor bepaalde workloads, zoals query's die gebruikmaken van grote scans. Als prestatievermindering optreedt nadat de verkleiningsbewerking is voltooid, kunt u indexonderhoud overwegen om indexen opnieuw te bouwen. Houd er rekening mee dat voor het herbouwen van indexen vrije ruimte in de database is vereist en daarom de toegewezen ruimte kan worden vergroot, waardoor het effect van de verkleiningsbewerking wordt tegengehouden.

Zie Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruik te verminderen voor meer informatie.

Een logboekbestand toevoegen of vergroten

U kunt ruimte krijgen door het bestaande logboekbestand te vergroten (als schijfruimte toestaat) of door een logboekbestand toe te voegen aan de database, meestal op een andere schijf. Eén transactielogboekbestand is voldoende, tenzij de logboekruimte opraakt en schijfruimte ook op het volume met het logboekbestand opraakt.

  • Als u een logboekbestand aan de database wilt toevoegen, gebruikt u de ADD LOG FILE component van de ALTER DATABASE instructie. Met deze actie kan het logboek groeien.
  • Als u het logboekbestand wilt vergroten, gebruikt u de MODIFY FILE component van de ALTER DATABASE instructie, waarbij u de SIZE en MAXSIZE syntaxis opgeeft. Zie ALTER DATABASE de opties voor bestanden en bestandsgroepen (Transact-SQL) voor meer informatie.

Zie Aanbevelingen voor meer informatie.

Grootte van tempdb-transactielogboek optimaliseren

Door een serverexemplaar opnieuw op te starten, wordt het transactielogboek van de database tempdb teruggebracht naar de oorspronkelijke grootte van vóór automatische groei. Deze formaatwijziging kan de prestaties van het tempdb transactielogboek verlagen.

U kunt deze overhead voorkomen door de grootte van het transactielogboek te verhogen nadat u het tempdb serverexemplaar hebt gestart of opnieuw hebt opgestart. Zie Tempdb-database voor meer informatie.

Groei van transactielogboekbestanden beheren

Gebruik de instructie ALTER DATABASE(Transact-SQL) bestands- en bestandsgroepopties om de groei van een transactielogbestand te beheren. Let op het volgende:

  • Gebruik de SIZE optie om de huidige bestandsgrootte te wijzigen in kB-, MB-, GB- en TB-eenheden.
  • Als u de toename van de groei wilt wijzigen, gebruikt u de FILEGROWTH optie. Een waarde van 0 geeft aan dat automatische groei wordt uitgeschakeld en dat er geen extra ruimte is toegestaan. Gebruik de MAXSIZE optie om de maximale grootte van een logboekbestand in kB-, MB-, GB- en TB-eenheden te bepalen of om groei in te stellen op UNLIMITED.

Zie Aanbevelingen voor meer informatie.

Aanbevelingen

Hier volgen enkele algemene aanbevelingen om rekening mee te houden wanneer u met transactielogboekbestanden werkt:

  • De automatische groei (automatische groei) van het transactielogboek, zoals ingesteld door de FILEGROWTH optie, moet groot genoeg zijn om te blijven voldoen aan de behoeften van de workloadtransacties. De toename van het bestand in een logboekbestand moet voldoende groot zijn om frequente uitbreiding te voorkomen. Een goede tip om een transactielog correct te dimensioneren, is het monitoren van de hoeveelheid logruimte die tijdens het volgende wordt gebruikt:

    • De tijd die nodig is om een volledige back-up uit te voeren, omdat er geen logboekback-ups kunnen worden uitgevoerd totdat deze is voltooid.
    • De tijd die nodig is voor de grootste indexonderhoudsbewerkingen.
    • De tijd die nodig is om de grootste batch in een database uit te voeren.

  • Wanneer u automatische groei instelt voor gegevens en logboekbestanden met behulp van de FILEGROWTH optie, is het misschien beter om deze in te stellen in plaats van percentage om een betere controle over de groeiverhouding mogelijk te maken, omdat een percentage een steeds groter wordend bedrag is.

    • In versies vóór SQL Server 2022 (16.x) kunnen transactielogboeken geen gebruik maken van instant bestandsinitialisatie, dus uitgebreide logboekgroeitijden zijn vooral essentieel.

    • Vanaf SQL Server 2022 (16.x) (alle edities) en in Azure SQL Database kunnen groeibeurtenissen van het transactielogboek tot 64 MB gebruikmaken van directe bestandsinitialisatie. De standaard incrementele grootte voor automatische groei voor nieuwe databases is 64 MB. Gebeurtenissen voor automatische groei van transactielogboekbestanden van meer dan 64 MB kunnen geen gebruikmaken van onmiddellijke bestandsinitialisatie.

    • Als aanbevolen werkwijze stelt u de waarde van de optie voor transactielogboeken niet hoger in dan FILEGROWTH 1.024 MB. De standaardwaarden voor de FILEGROWTH optie zijn:

      Versie Standaardwaarden
      Vanaf SQL Server 2016 (13.x) Gegevens: 64 MB. Logboekbestanden: 64 MB.
      Vanaf SQL Server 2005 (9.x) Gegevens: 1 MB. Logboekbestanden: 10%.
      Vóór SQL Server 2005 (9.x) Gegevens: 10%. Logboekbestanden: 10%.

  • Een kleine toename van automatische groei kan te veel kleine VLF's genereren en de prestaties verminderen. Als u de optimale VLF-distributie wilt bepalen voor de huidige transactielogboekgrootte van alle databases in een bepaald exemplaar en de vereiste groeiverhogingen om de vereiste grootte te bereiken, raadpleegt u dit script voor het analyseren en herstellen van VLF's, geleverd door het SQL Tiger Team.

  • Een grote toename van automatische groei kan twee problemen veroorzaken:

    • Dit kan ertoe leiden dat de database wordt onderbroken terwijl de nieuwe ruimte wordt toegewezen, waardoor er mogelijk time-outs voor query's ontstaan.
    • Het kan te weinig en grote VLF's genereren en kan ook van invloed zijn op de prestaties. Als u de optimale VLF-distributie wilt bepalen voor de huidige transactielogboekgrootte van alle databases in een bepaald exemplaar en de vereiste groeiverhogingen om de vereiste grootte te bereiken, raadpleegt u dit script voor het analyseren en herstellen van VLF's, geleverd door het SQL Tiger Team.

  • Zelfs als automatische groei is ingeschakeld, kunt u een bericht ontvangen dat het transactielogboek vol is als het niet snel genoeg kan groeien om aan de behoeften van uw query te voldoen. Zie (Transact-SQL) bestands- en bestandsgroepopties voor meer informatie over het wijzigen van de groeitoenameALTER DATABASE.

  • Als u meerdere logboekbestanden in een database hebt, worden de prestaties op geen enkele manier verbeterd, omdat de transactielogboekbestanden geen proportionele opvulling gebruiken, zoals gegevensbestanden in dezelfde bestandsgroep.

Logboekbestanden kunnen automatisch worden verkleind. We raden deze configuratie echter niet aan en de AUTO_SHRINK database-eigenschap is standaard ingesteld op FALSE. Als AUTO_SHRINK deze optie is ingesteld op TRUE, vermindert automatisch verkleinen de grootte van een bestand alleen wanneer meer dan 25 procent van de ruimte niet wordt gebruikt.

  • Het bestand is verkleind tot de grootte waarop slechts 25 procent van het bestand ongebruikte ruimte heeft of tot de oorspronkelijke grootte van het bestand, afhankelijk van wat groter is.
  • Zie AUTO_SHRINK en ALTER DATABASE SET voor informatie over het wijzigen van de instelling voor de eigenschap.

Verwante inhoud

  • Last updated on