Sdílet prostřednictvím

Správa velikosti souboru transakčního protokolu

platí pro:SQL Server

Tento článek popisuje, jak monitorovat velikost transakčního protokolu SQL Serveru, zmenšit transakční protokol, přidat nebo zvětšit soubor transakčního protokolu, optimalizovat tempdb rychlost růstu transakčního protokolu a řídit růst souboru transakčního protokolu.

Tento článek se týká SQL Serveru. I když je tento proces podobný, pro správu prostoru souborů v Azure SQL si přečtěte:

Pochopit typy úložného prostoru pro databázi

Pro správu prostoru souborů databáze je důležité porozumět následujícímu množství úložného prostoru.

Množství databáze Definition Comments
Využité datové místo Prostor použitý k ukládání databázových dat. Obecně platí, že využité místo se zvyšuje při vkládání a snižuje se při odstraňování. V některých případech použitý prostor při vkládání nebo mazání data nezmění, v závislosti na množství a vzoru dat zapojených do operace a jakékoli fragmentaci. Například odstranění jednoho řádku z každé datové stránky nemusí nutně snížit využité místo.
Přidělený datový prostor Formátované místo souboru zpřístupněné pro ukládání dat do databáze. Množství přiděleného prostoru se automaticky zvětšuje, ale po odstranění dat se nikdy nezmenší. Toto chování zajišťuje, aby budoucí vložení bylo rychlejší, protože mezera nemusí být přeformátovaná.
Přidělený datový prostor, ale nevyužitý Rozdíl mezi přidělenou částkou a využitou datovým prostorem. Toto množství představuje maximální volné místo, které může uvolnit zmenšování datových souborů databáze.
Maximální velikost dat Maximální velikost místa pro ukládání databázových dat. Množství přiděleného datového prostoru se nemůže zvětšit nad rámec maximální velikosti dat.

Následující diagram znázorňuje vztahy mezi různými typy prostoru úložiště pro databázi.

Diagram znázorňující vztahy mezi různými typy prostoru úložiště pro databázi

Dotazování na jednoúčelovou databázi s informacemi o prostoru souborů

Pomocí následujícího dotazu vrátíte přidělený prostor databázového souboru a množství nevyužitého místa přiděleného. Jednotky výsledku dotazu jsou v 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;

Monitorování využití prostoru logu

Monitorujte využití prostoru protokolu pomocí sys.dm_db_log_space_usage. Toto dynamické spravované zobrazení vrací informace o množství aktuálně využívaného místa v protokolu a ukazuje, kdy transakční protokol potřebuje prořezání.

Pro informace o aktuální velikosti souboru protokolu, jeho maximální velikosti a možnosti automatického zvětšování souboru lze využít sloupce size, max_size a growth pro tento soubor protokolu v sys.database_files.

Důležité

Vyhněte se přetížení disku pro protokoly. Ujistěte se, že úložiště protokolů splňuje požadavky na IOPS a nízkou latenci pro vaše transakční zatížení.

Zmenšení souboru protokolu

Zmenšením souboru protokolu zmenšíte jeho fyzickou velikost vrácením volného místa do operačního systému. Zmenšení má význam pouze tehdy, když soubor transakčního protokolu obsahuje nevyužité místo.

Pokud je soubor protokolu plný, pravděpodobně kvůli otevřeným transakcím, prozkoumejte, co brání zkrácení transakčního protokolu.

Upozornění

Operace zmenšení by se neměly považovat za běžnou operaci údržby. Data a soubory protokolů, které se zvětšují kvůli pravidelným opakovaným obchodním operacím, nevyžadují operace zmenšení. Zmenšení příkazů má vliv na výkon databáze při spuštění. Měly by se spouštět v době nízkého zatížení. Nedoporučujeme zmenšit datové soubory, pokud běžná úloha aplikace způsobí, že se soubory znovu zvětší na stejnou přidělenou velikost.

Mějte na paměti potenciální negativní dopad na výkon zmenšení databázových souborů. Podívejte se na údržbu indexů po zmenšení.

Před zmenšením transakčního protokolu mějte na paměti faktory, které můžou zpozdit zkrácení protokolu. Pokud se po zmenšení protokolu opět vyžaduje úložný prostor, transakční protokol se znovu zvětší a během operací růstu protokolu se zvýší náklady na výkon. Další informace najdete v tématu Doporučení.

Soubor protokolu můžete zmenšit pouze v době, kdy je databáze online a alespoň jeden soubor virtuálního protokolu (VLF) je zdarma. V některých případech může být zmenšení protokolu možné pouze po dalším zkrácení protokolu.

Některé faktory, jako je dlouhotrvající transakce, mohou uchovávat VLF aktivní po delší dobu, mohou omezit zmenšení protokolu nebo dokonce zabránit zmenšení protokolu vůbec. Další informace naleznete v tématu Faktory, které mohou zpozdit zkrácení protokolu.

Zmenšení souboru protokolu odebere jeden nebo více souborů VLF , které neobsahují žádnou část logického protokolu (to znamená neaktivní soubory VLF). Když zmenšíte soubor transakčního protokolu, neaktivní soubory VLF se odeberou z konce souboru protokolu, aby se protokol snížil na přibližně cílovou velikost.

Další informace o operacích zmenšování naleznete v následujících zdrojích:

Zmenšení souboru protokolu (bez zmenšení databázových souborů)

Monitorování událostí zmenšení souboru protokolu

Monitorování prostoru protokolu

Údržba indexu po operaci zmenšení

Indexy se můžou po dokončení operace zmenšení s datovými soubory fragmentovat. Tato fragmentace snižuje efektivitu optimalizace výkonu u určitých úloh, jako jsou dotazy, které používají velké skeny. Pokud po dokončení operace zmenšení dojde ke snížení výkonu, zvažte údržbu indexů při opětovném sestavení indexů. Mějte na paměti, že opětovné sestavení indexu vyžaduje volné místo v databázi, a proto může zvýšit přidělený prostor, což snižuje účinek operace zmenšení.

Další informace najdete v tématu Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků.

Přidání nebo zvětšení souboru protokolu

Místo můžete získat zvětšením existujícího souboru protokolu (pokud to umožňuje místo na disku) nebo přidáním souboru protokolu do databáze, obvykle na jiném disku. Jeden soubor transakčního protokolu stačí, pokud není nedostatek místa na protokolu a na svazku, který obsahuje soubor protokolu, dochází také místo na disku.

  • Pokud chcete do databáze přidat soubor protokolu, použijte ADD LOG FILE klauzuli ALTER DATABASE příkazu. Tato akce umožňuje zvětšit protokol.
  • Pokud chcete zvětšit soubor protokolu, použijte MODIFY FILE klauzuli ALTER DATABASE příkazu a zadejte SIZE a MAXSIZE syntaxi. Další informace naleznete v tématu ALTER DATABASE (Transact-SQL) soubor a možnosti skupiny souborů.

Další informace najdete v tématu Doporučení.

Optimalizace velikosti transakčního protokolu tempdb

Restartováním instance serveru obnovíte velikost transakčního protokolu databáze tempdb na původní před automatickým růstem. Tato změna velikosti může snížit výkon transakčního tempdb protokolu.

Této režii se můžete vyhnout zvýšením velikosti transakčního tempdb protokolu po spuštění nebo restartování instance serveru. Další informace naleznete v tempdb databázi.

Kontrola růstu souboru protokolu transakcí

Ke správě růstu souboru transakčního protokolu použijte soubor ALTER DATABASE (Transact-SQL) a příkaz možnosti skupiny souborů . Všimněte si následujících věcí:

  • SIZE Pomocí možnosti můžete změnit aktuální velikost souboru v jednotkách KB, MB, GB a TB.
  • Pokud chcete změnit přírůstek růstu, použijte tuto FILEGROWTH možnost. Hodnota 0 označuje, že automatický růst je nastavený na vypnutý a není povolen žádný nadbytečný prostor. MAXSIZE Pomocí možnosti můžete řídit maximální velikost souboru protokolu v kB, MB, GB a TB jednotky nebo nastavit růst na UNLIMITED.

Další informace najdete v tématu Doporučení.

Recommendations

Při práci se soubory transakčních protokolů byste měli zvážit následující obecná doporučení:

  • Automatický růst (autogrowth) inkrementace transakčního protokolu, jak je nastavená možností FILEGROWTH , musí být dostatečně velký, aby zůstal před potřebami transakcí úloh. Přírůstek růstu protokolového souboru by měl být dostatečně velký, aby nedocházelo k častému rozšiřování. Dobrým tipem pro správné určení velikosti transakčního protokolu je monitorování množství protokolu obsazeného během:

    • Doba potřebná ke spuštění úplného zálohování, protože zálohování protokolů nemůže nastat, dokud se nedokončí.
    • Doba potřebná pro největší operace údržby indexů.
    • Doba potřebná ke spuštění největší dávky v databázi.

  • Když nastavíte automatické zvětšování dat a souborů protokolů pomocí FILEGROWTH této možnosti, může být lepší nastavit velikost místo procenta , aby bylo možné lépe řídit poměr růstu, protože procento je stále rostoucí množství.

    • Ve verzích starších než SQL Server 2022 (16.x) nemohou protokoly transakcí používat okamžitou inicializaci souborů, takže prodloužené doby růstu protokolu jsou obzvláště kritické.

    • Počínaje SQL Serverem 2022 (16.x) (všechny edice) a v Azure SQL Database, okamžitá inicializace souborů může být výhodná při růstu transakčních protokolů až do 64 MB. Výchozí velikost automatického zvětšování pro nové databáze je 64 MB. Události automatického zvětšování souboru transakčního protokolu větší než 64 MB nemohou těžit z okamžité inicializace souborů.

    • Osvědčeným postupem je nenastavovat FILEGROWTH hodnotu možnosti nad 1 024 MB pro protokoly transakcí. Výchozí hodnoty pro možnost FILEGROWTH jsou:

      Version Výchozí hodnoty
      Počínaje SQL Serverem 2016 (13.x) Data: 64 MB. Soubory protokolu: 64 MB.
      Počínaje SQL Serverem 2005 (9.x) Data: 1 MB. Soubory protokolu: 10%.
      Před SQL Serverem 2005 (9.x) Data: 10%. Soubory protokolu: 10%.

  • Malý automatický nárůst může generovat příliš mnoho malých VLF a může snížit výkon. Pokud chcete určit optimální distribuci VLF pro aktuální velikost transakčního protokolu všech databází v dané instanci a požadované přírůstky růstu pro dosažení požadované velikosti, prohlédněte si tento skript pro analýzu a opravu souborů VLF, které poskytuje tým SQL Tiger.

  • Velký přírůstek automatického růstu může způsobit dva problémy:

    • Může způsobit pozastavení databáze při přidělení nového prostoru, což může způsobit vypršení časových limitů dotazů.
    • Může generovat příliš málo a velké VLF a může také ovlivnit výkon. Pokud chcete určit optimální distribuci VLF pro aktuální velikost transakčního protokolu všech databází v dané instanci a požadované přírůstky růstu pro dosažení požadované velikosti, prohlédněte si tento skript pro analýzu a opravu souborů VLF, které poskytuje tým SQL Tiger.

  • I s povoleným automatickým zvětšováním můžete obdržet zprávu, že transakční protokol je plný, pokud nemůže dostatečně rychle narůst, aby vyhovoval potřebám vašeho dotazu. Další informace o změně přírůstku růstu naleznete v tématu ALTER DATABASE (Transact-SQL) možnosti souboru a skupiny souborů.

  • Pokud máte v databázi více souborů protokolu, nijak nezlepšíte výkon, protože soubory transakčních protokolů nepoužívají proporcionální výplň , jako jsou datové soubory ve stejné skupině souborů.

Soubory protokolů je možné nastavit tak, aby se automaticky zmenšují. Tuto konfiguraci ale nedoporučujeme a AUTO_SHRINK vlastnost databáze je ve výchozím nastavení nastavená na FALSE. Pokud AUTO_SHRINK je nastavená hodnota PRAVDA, automatické zmenšení zmenší velikost souboru pouze v případě, že je nevyužito více než 25 procent místa.

Související obsah

  • Last updated on