Dela via

Hantera storleken på transaktionsloggfilen

Gäller för:SQL Server

Den här artikeln beskriver hur du övervakar storleken på SQL Server-transaktionsloggen, krymper transaktionsloggen, lägger till eller förstorar en transaktionsloggfil, optimerar transaktionsloggens tempdb tillväxthastighet och kontrollerar tillväxten för en transaktionsloggfil.

Den här artikeln gäller för SQL Server. Även om processen är liknande, för filutrymmeshantering i Azure SQL, se:

Förstå typer av lagringsutrymme för en databas

Det är viktigt att förstå följande lagringsutrymmeskvantiteter för att hantera filutrymmet i en databas.

Databaskvantitet Definition Comments
Datautrymme som används Det utrymme som används för att lagra databasdata. I allmänhet ökar utrymmet som används vid infogningar och minskar vid borttagningar. I vissa fall ändras inte det utrymme som används vid infogningar eller borttagningar, beroende på mängden och mönstret för data som ingår i åtgärden och eventuell fragmentering. Om du till exempel tar bort en rad från varje datasida minskar inte nödvändigtvis det utrymme som används.
Allokerat datautrymme Det formaterade filutrymmet som görs tillgängligt för lagring av databasdata. Mängden allokerat utrymme växer automatiskt men minskar aldrig efter borttagningar. Det här beteendet säkerställer att framtida infogningar blir snabbare eftersom utrymmet inte behöver formateras om.
Allokerat datautrymme men oanvänt Skillnaden mellan mängden allokerat utrymme och det datautrymme som används. Den här kvantiteten representerar det maximala lediga utrymme som krympande databasdatafiler kan frigöra.
Maximal datastorlek Den maximala mängden utrymme för lagring av databasdata. Mängden allokerat datautrymme kan inte öka utöver data maxstorleken.

Följande diagram illustrerar relationerna mellan de olika typerna av lagringsutrymme för en databas.

Diagram som illustrerar relationerna mellan de olika typerna av lagringsutrymme för en databas.

Fråga en enskild databas om information om filutrymme

Använd följande fråga för att returnera mängden allokerat databasfilutrymme och mängden oanvänt utrymme som allokerats. Enheter i frågeresultatet är i 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;

Övervaka användning av loggutrymme

Övervaka användningen av loggutrymme med hjälp av sys.dm_db_log_space_usage. Denna DMV returnerar information om mängden loggutrymme som används för närvarande och anger när transaktionsloggen behöver trunkeras.

Du kan också använda kolumnerna size, max_sizeoch för loggfilen i sys.database_files om du vill ha information om den aktuella loggfilens storlek, maximal storlek och growth alternativet för automatisk inväxt.

Viktigt!

Undvik att överbelasta loggdisken. Se till att logglagringen klarar kraven på IOPS och låg svarstid för transaktionsbelastningen.

Krympa en loggfil

Krymp loggfilen för att minska den fysiska storleken genom att returnera ledigt utrymme till operativsystemet. En krympning gör bara skillnad när en transaktionsloggfil innehåller outnyttjat utrymme.

Om loggfilen är full, förmodligen på grund av öppna transaktioner, undersöker du vad som förhindrar att transaktionsloggen trunkeras.

Försiktighet

Krympåtgärder bör inte betraktas som en vanlig underhållsåtgärd. Data och loggfiler som växer på grund av regelbundna återkommande affärsåtgärder kräver inte krympningsåtgärder. Krympa kommandon påverkar databasprestanda när de körs. De bör köras under perioder med låg användning. Vi rekommenderar inte att du krymper datafiler om en vanlig programarbetsbelastning gör att filerna växer till samma allokerade storlek igen.

Tänk på den potentiella negativa prestandapåverkan av krympande databasfiler. Se Indexunderhåll efter krympning.

Innan du krymper transaktionsloggen bör du tänka på faktorer som kan fördröja loggtrunkeringen. Om lagringsutrymme krävs igen efter att en logg krympt växer transaktionsloggen igen, vilket ger prestandaomkostnader under loggtillväxtåtgärder. Mer information finns i Rekommendationer.

Du kan bara krympa en loggfil när databasen är online och minst en virtuell loggfil (VLF) är kostnadsfri. I vissa fall kan det vara möjligt att krympa loggen först efter nästa loggtrunkering.

Vissa faktorer, till exempel en tidskrävande transaktion, kan hålla VLFs aktiva under en längre period, kan begränsa loggkrympningen eller till och med förhindra att loggen krymper alls. Mer information finns i Faktorer som kan fördröja loggtrunkering.

Om du krymper en loggfil tas en eller flera VLF:er bort som inte innehåller någon del av den logiska loggen (dvs. inaktiva VLF:er). När du krymper en transaktionsloggfil tas inaktiva VLFs bort från slutet av loggfilen för att minska loggen till ungefär målstorleken.

Mer information om krympningsåtgärder finns i följande resurser:

Krympa en loggfil (utan att krympa databasfiler)

Övervaka krympningshändelser för loggfiler

Övervaka loggutrymme

Indexunderhåll efter en krympningsåtgärd

Index kan bli fragmenterade när en krympningsåtgärd har slutförts mot datafiler. Den här fragmenteringen minskar deras effektivitet för prestandaoptimering för vissa arbetsbelastningar, till exempel frågor som använder stora genomsökningar. Om prestandaförsämringen inträffar när krympningsåtgärden är klar bör du överväga indexunderhåll för att återskapa index. Tänk på att återskapade index kräver ledigt utrymme i databasen och därmed kan öka det allokerade utrymmet, vilket motverkar effekten av krympningsåtgärden.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

Lägga till eller förstora en loggfil

Du kan få utrymme genom att förstora den befintliga loggfilen (om diskutrymme tillåter) eller lägga till en loggfil i databasen, vanligtvis på en annan disk. En transaktionsloggfil räcker såvida inte loggutrymmet håller på att ta slut och diskutrymmet också håller på att ta slut på den volym som innehåller loggfilen.

  • Om du vill lägga till en loggfil i databasen använder du instruktionens ADD LOG FILE sats ALTER DATABASE . Med den här åtgärden kan loggen växa.
  • Om du vill förstora loggfilen använder du instruktionens MODIFY FILEALTER DATABASE sats och anger syntaxen SIZE och MAXSIZE . Mer information finns i ALTER DATABASE (Transact-SQL) fil- och filgruppsalternativ.

Mer information finns i Rekommendationer.

Optimera tempdb-transaktionsloggens storlek

Om du startar om en serverinstans ändrar du storleken på transaktionsloggen tempdb för databasen till den ursprungliga storleken för automatisk storlek. Den här storleksändringen kan minska prestandan för transaktionsloggen tempdb .

Du kan undvika detta genom att öka transaktionsloggens tempdb storlek när du har startat eller startat om serverinstansen. Mer information finns i Tempdb-databasen.

Kontrollera transaktionsloggfilens tillväxt

Använd instruktionen ALTER DATABASE (Transact-SQL) för fil- och filgruppsalternativ för att hantera tillväxten av en transaktionsloggfil. Observera följande:

  • Använd alternativet SIZE för att ändra den aktuella filstorleken i KB-, MB-, GB- och TB-enheter.
  • Om du vill ändra tillväxtökningen använder du alternativet FILEGROWTH . Värdet 0 anger att automatisk tillväxt är inställd på av och att inget extra utrymme tillåts. Använd alternativet MAXSIZE för att styra den maximala storleken på en loggfil i KB-, MB-, GB- och TB-enheter eller för att ange tillväxt till UNLIMITED.

Mer information finns i Rekommendationer.

Recommendations

Följande är några allmänna rekommendationer att tänka på när du arbetar med transaktionsloggfiler:

  • Den automatiska tillväxtökningen (automatisk tillväxt) i transaktionsloggen, som anges av FILEGROWTH alternativet, måste vara tillräckligt stor för att ligga före arbetsbelastningstransaktionernas behov. Ökning av filtillväxt i en loggfil bör vara tillräckligt stor för att undvika frekvent expansion. Ett bra tips för korrekt storleksändring av en transaktionslogg är att övervaka mängden logg som upptas under:

    • Den tid som krävs för att köra en fullständig säkerhetskopia, eftersom loggsäkerhetskopior inte kan ske förrän den har slutförts.
    • Den tid som krävs för de största indexunderhållsåtgärderna.
    • Den tid som krävs för att köra den största batchen i en databas.

  • När du anger automatisk tillväxt för data och loggfiler med hjälp FILEGROWTH av alternativet kan det vara bättre att ange den i storlek i stället för procent för att ge bättre kontroll över tillväxtkvoten, eftersom en procentandel är en ständigt växande mängd.

    • I versioner före SQL Server 2022 (16.x) kan transaktionsloggar inte använda omedelbar filinitiering, så utökade loggtillväxttider är särskilt kritiska.

    • Från och med SQL Server 2022 (16.x) (alla utgåvor) och i Azure SQL Database kan omedelbar filinitiering gynna transaktionsloggens tillväxthändelser på upp till 64 MB. Standardvärdet för automatisk storleksökning för nya databaser är 64 MB. Händelser som är större än 64 MB kan inte dra nytta av omedelbar filinitiering.

    • Vi rekommenderar att du inte anger FILEGROWTH alternativvärdet över 1 024 MB för transaktionsloggar. Standardvärdena FILEGROWTH för alternativet är:

      Utgåva Standardvärden
      Börjar med SQL Server 2016 (13.x) Data: 64 MB. Loggfiler: 64 MB.
      Börjar med SQL Server 2005 (9.x) Data: 1 MB. Loggfiler: 10%.
      Före SQL Server 2005 (9.x) Data: 10%. Loggfiler: 10%.

  • En liten automatisk ökning kan generera för många små VLF:er och kan minska prestanda. Information om hur du fastställer den optimala VLF-distributionen för den aktuella transaktionsloggstorleken för alla databaser i en viss instans och de tillväxtökningar som krävs för att uppnå den nödvändiga storleken finns i det här skriptet för att analysera och åtgärda VLF:er som tillhandahålls av SQL Tiger Team.

  • Ett stort autogrowth-steg kan orsaka två problem:

    • Det kan göra att databasen pausas medan det nya utrymmet allokeras, vilket kan orsaka tidsgränser för frågor.
    • Det kan generera för få och stora VLF:er och kan även påverka prestanda. Information om hur du fastställer den optimala VLF-distributionen för den aktuella transaktionsloggstorleken för alla databaser i en viss instans och de tillväxtökningar som krävs för att uppnå den nödvändiga storleken finns i det här skriptet för att analysera och åtgärda VLF:er som tillhandahålls av SQL Tiger Team.

  • Även om autogrowth är aktiverat kan du få ett meddelande om att transaktionsloggen är full om den inte kan växa tillräckligt snabbt för att uppfylla behoven i din fråga. Mer information om hur du ändrar tillväxtökningen finns i ALTER DATABASE (Transact-SQL) fil- och filgruppsalternativ.

  • Att ha flera loggfiler i en databas förbättrar inte prestandan på något sätt, eftersom transaktionsloggfilerna inte använder proportionell fyllning som datafiler i samma filgrupp.

Loggfiler kan ställas in för att krympa automatiskt. Vi rekommenderar dock inte den här konfigurationen och databasegenskapen AUTO_SHRINK är inställd på FALSE som standard. Om AUTO_SHRINK värdet är TRUE minskar automatisk krympning endast storleken på en fil när mer än 25 procent av dess utrymme inte används.

Relaterat innehåll