SQLShack

tento článek zkoumá použití více souborů protokolu transakcí serveru SQL Server a proces odebrání souboru protokolu sekundárních transakcí.

Úvod

databáze SQL Serveru ve výchozím nastavení obsahuje primární datový soubor a soubor protokolu transakcí. Je to dobrá praxe konfigurace více datových souborů a rozdělení dat na více datových souborů. Tyto datové soubory můžeme mít v samostatných úložných jednotkách, abychom měli více IO. Pomáhá správě dat, zlepšuje výkon, plánuje záložní přístupy podle skupin souborů.

pro databázi můžeme mít také více souborů protokolu transakcí.

SQL Server používá více souborů protokolu transakcí v sekvenčním pořadí. Předpokládejme, že máme dva soubory protokolu transakcí. SQL Server používá jeden soubor protokolu najednou a jakmile je plný, SQL Server používá jiný soubor protokolu. Vzhledem k tomu, že SQL Server nepoužívá paralelně, nedostáváme z něj žádný výkonový přínos. V ideálním případě bychom měli mít pouze jeden soubor protokolu transakcí na databázi.

předpokládejme, že máme výrobní databázi a dostaneme kritické upozornění, že na disku dochází místo. Po vyšetřování jsme zjistili, že tato jednotka obsahuje soubor protokolu transakcí. Díky aktivním transakcím je tento soubor protokolu transakcí SQL Server plný. Snažili jsme se zmenšit soubor protokolu, ale nefungovalo to. Zálohujeme také protokol transakcí, ale kvůli aktivním transakcím nemohl uvolnit potřebný prostor. Přidání dalšího souboru protokolu transakcí na samostatný disk s volným místem tento problém vyřeší.

vzhledem k tomu, že SQL Server používá sériový režim pro zápis dat do souboru protokolu transakcí, měli bychom později odstranit další soubor protokolu. Pojďme prozkoumat proces odebrání dalšího souboru protokolu.

vytvořte databázi s více soubory protokolu transakcí SQL Server

Připojte se k instanci SQL v SQL Server Management Studio. Klepněte pravým tlačítkem myši na uzel databáze v podokně Průzkumníka objektů a klikněte na příkaz Nová databáze:

otevře se nové okno databáze. Zadejte název databáze a přidejte další soubor protokolu transakcí. Pro účely testování jsme zakázali automatický růst souborů protokolu transakcí. Počáteční velikost souboru protokolu transakcí je 8MB:

po nastavení všeho klikněte na tlačítko OK pro vytvoření databáze ve výchozím adresáři souborů dat/protokolu. Vytvořte tabulku a vložte data do:

Zobrazení stavu souboru virtuálního protokolu v souborech protokolu transakcí SQL Server

v článku Architektura protokolu transakcí SQL Server jsme prozkoumali interní soubor protokolu transakcí. Každý soubor protokolu transakcí se skládá z více souborů virtuálního protokolu. Soubor protokolu transakcí je kombinací více souborů virtuálního protokolu (VLF). Následující snímek obrazovky ukazuje fyzickou a logickou architekturu souboru protokolu:

SQL Server stats databáze s minimálním VLF na základě počáteční velikosti protokolu a auto grow souboru (na základě konfigurace auto-růst). Na následujícím obrázku se podíváme na soubor protokolu transakcí serveru SQL Server:

v případě jediného souboru protokolu transakcí používá SQL Server kruhovou cestu k souboru virtuálního protokolu. Počet souborů virtuálního protokolu a jejich stav můžeme zkontrolovat pomocí následujícího:

1. DBCC LOGINFO(‚databáze‘) – je to starý příkaz a pracuje se všemi verzemi SQL Server
2. Dynamic management view sys. dm_db_log_info (DBID). Je k dispozici z SQL Server 2016 SP2 nebo novější

některý z příkazů může být použit pro kontrolu VLF. Pro tento článek použijeme Dynamic management view (DMV):

na výše uvedeném snímku obrazovky jsme ověřili, že naše vzorová databáze obsahuje dva soubory protokolu transakcí (file_id 2 a file_id 3).

• File_id 2 má aktivní VLF (vlf_active=1 a vlf_status=2)
• File_id 3 nemá aktivní VLF (vlf_active=0 a vlf_status=2)

vložíme do tabulky několik dalších záznamů, aby se aktivní VLF změnil:

monitorování využití prostoru protokolu transakcí a stav VLF bude provedeno pomocí DMV (sys. dm_db_log_space_usage):

• stav VLF

  1 2	vyberte DB_name () jako DatabaseName,File_ID jako transaction_log_file_ID, vlf_active , vlf_status z sys. dm_db_log_info (DB_ID (‚MultileLogFiles‘));

• využití protokolu transakcí (použité a volné místo)

  1 2 3 4 5

  vyberte total_log_size_in_bytes * 1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes-used_log_space_in_bytes)*1.0/1024/1024 jako free_log_space_in_MB z sys. dm_db_log_space_usage;

získáme následující výstup z výše uvedených dotazů:

• na levé straně vidíme, že jakmile se primární soubor protokolu (file_id 2) naplní, přesune se do dalšího souboru protokolu (file_id 3). V tomto okamžiku máme aktivní VLF v primárních i sekundárních souborech protokolu. V databázi s modelem úplné obnovy potřebujeme zálohu protokolu transakcí, aby označila VLF jako neaktivní
• výstup na pravé straně (pomocí DMV) ukazuje použitý soubor protokolu transakcí prostor 8.51 MB. Jak si vzpomínáte, nastavili jsme velikost 8 MB pro každý soubor protokolu transakcí a deaktivovali jsme automatický růst protokolu. Když je primární soubor protokolu plný (na 8 MB), SQL Server přepne na sekundární soubor protokolu transakcí

Odebrat sekundární soubor protokolu transakcí serveru SQL

Chcete-li odstranit soubor protokolu sekundárních transakcí (ID souboru 3), použijeme upravený příkaz Alter database.

přidáme klauzuli odebrat soubor a určíme soubor, který chceme odstranit:

provedení tohoto příkazu bude mít za následek následující chybovou zprávu:

Poznámka: aktivní soubor protokolu transakcí nelze odstranit.

dříve jsme viděli, že jakmile se primární soubor protokolu naplní, SQL Server používá sekundární soubor protokolu. Potřebujeme, aby byl sekundární protokol transakcí prázdný, abychom ho mohli odstranit.

v databázi SQL s modelem úplné obnovy používáme zálohy protokolu transakcí, takže SQL Server může protokoly zkrátit. V závislosti na velikosti protokolu transakcí, aktivní VLF a aktivní transakci můžeme potřebovat více záloh protokolu.

provedeme úplnou zálohu databáze a protokolu transakcí:

• úplné zálohování databáze

  1	zálohování databáze MultipleLogFiles na disk=’C:\Temp\MultipleLogFiles.bak‘

• zálohování protokolu transakcí

  1	zálohování Log MultipleLogFiles na disk=’C:\Temp\MultipleLogFiles_log.trn‘

po dokončení zálohy ověřte stav VLF. Aktivní VLF by měl být v prvním souboru protokolu (primární), abychom mohli odstranit sekundární soubor protokolu. Ověřili jsme, že je aktivní pouze primární soubor protokolu (file_id 2) (Stav VLF 2):

nyní by měl být soubor protokolu sekundárních transakcí bez problémů odstraněn. Pojďme znovu spustit upravený příkaz Alter database. Soubor protokolu sekundárních transakcí bude odstraněn:

jakmile je odstraněn soubor protokolu sekundárních transakcí, ověřte jej pomocí GUI a T-SQL s system view sys.databáze:

odstraněný soubor protokolu sekundárních transakcí je stále přítomen podle následujícího snímku obrazovky:

nyní klikněte pravým tlačítkem myši na databázi a zobrazte existující soubory. Odstraněný soubor protokolu transakcí vidíme také v GUI. Ale proč?

pojďme znovu spustit upravený příkaz Alter database a uvidíme, jestli SQL Server znovu odstraní soubor protokolu transakcí.

dostali jsme zprávu, že SQL Server nemohl najít zadaný soubor protokolu:

SQL Server odstraní soubor protokolu transakcí po následném zálohování protokolu. Vezměme si další zálohu protokolu a ověřme, že soubor protokolu transakcí stále existuje:

ověřte soubor protokolu transakcí v metodách GUI i T-SQL. Vidíme, že odstraněný soubor protokolu transakcí se nyní nezobrazuje.

závěr

v tomto článku jsme zkoumali použití sekundárního protokolu transakcí serveru SQL a proces jeho odstranění. Měli byste se vyhnout použití více souborů protokolu transakcí, zejména v produkční databázi. Před plánováním jakékoli činnosti byste měli vzít zálohu databáze a udělat to v hodinách mimo produktivitu.

• Autor
• poslední příspěvky

jako MCSA certified a Microsoft Certified Trainer v Gurgaonu, Indie, s 13 let zkušeností, Rajendra pracuje pro řadu velkých společností zaměřených na optimalizaci výkonu, monitorování, vysoká dostupnost, a strategie a implementace obnovy po havárii. Je autorem stovek autoritativních článků o SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS / Amazon RDS, Git a souvisejících technologiích, které dosud vidělo více než 10m čtenářů.
je tvůrcem jedné z největších bezplatných online sbírek článků na jedno téma, se svou 50dílnou sérií na serveru SQL Server vždy ve skupinách dostupnosti. Na základě svého příspěvku do komunity SQL Server byl v letech 2020 a 2021 na SQLShack oceněn různými oceněními včetně prestižního „nejlepšího autora roku“.
Raj se vždy zajímá o nové výzvy, takže pokud potřebujete poradit s jakýmkoli tématem obsaženým v jeho spisech,může být dosažen v [email protected]
Zobrazit všechny příspěvky od Rajendra Gupta

nejnovější příspěvky od Rajendra Gupta (Zobrazit vše)

• použijte šablony ARM k nasazení instancí Azure container s obrázky SQL Server Linux-21. prosince 2021
• vzdálený přístup na plochu pro AWS RDS SQL Server s Amazon RDS Custom-14. prosince 2021
• uložte soubory SQL Serveru do trvalého úložiště pro instance Azure Container-prosinec 10, 2021