Delen via

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 ruimte die wordt gebruikt toe op invoegingen en vermindert het aantal 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 vertegenwoordigt de maximale vrije ruimte die databasegegevensbestanden kunnen terugvorderen.
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. Opdrachten verkleinen zijn van invloed op de databaseprestaties 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 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)

Gebeurtenissen voor het verkleinen van logboekbestanden 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 (Transact-SQL) bestands- en bestandsgroepopties voor meer informatie.

Zie Aanbevelingen voor meer informatie.

Grootte van tempdb-transactielogboek optimaliseren

Als u een serverexemplaren opnieuw start, wordt de grootte van het transactielogboek van de tempdb database gewijzigd in de oorspronkelijke grootte van vóór automatisch groeien. Deze grootte kan de prestaties van het tempdb transactielogboek verminderen.

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

Groei van transactielogboekbestanden beheren

Gebruik de instructie ALTER DATABASE (Transact-SQL) en bestandsgroepopties om de groei van een transactielogboekbestand 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 voor het goed aanpassen van de grootte van een transactielogboek is het bewaken van de hoeveelheid logboeken die tijdens het gebruik van het logboek 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 kan direct initialisatie van bestanden profiteren van groei-gebeurtenissen in transactielogboeken tot 64 MB. De standaard incrementele grootte voor automatische groei voor nieuwe databases is 64 MB. Automatische groei van transactielogboekbestanden die groter zijn dan 64 MB, kunnen niet profiteren van instant bestands initialisatie.

    • Stel de optiewaarde boven 1024 MB niet FILEGROWTH in voor transactielogboeken. 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 ALTER DATABASE (Transact-SQL) bestands- en bestandsgroepopties voor meer informatie over het wijzigen van de groeitoename.

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

Verwante inhoud