SQLShack

V tomto článku se budeme zabývat vlastností databáze SQL Server Auto Shrink, která umožňuje serveru SQL Server automaticky zmenšovat soubory databáze, pokud je v možnosti databáze nastavena hodnota True. Operaci automatického zmenšování provádí vlastnost serveru auto shrink database, která je hlavním předmětem tohoto článku.

Aby se zabránilo činnosti zmenšování datových/logových souborů vždy, když velikost souboru přesáhne volné místo v datovém souboru, musí DBA (správce databáze) v pravidelných intervalech zálohovat logové soubory. Zálohování celé databáze není dobrý nápad; je třeba vytvořit nebo nastavit také protokol transakcí. Pokud nebudete zálohovat transakční protokoly, bude jejich počet narůstat, až obsadí veškeré volné místo na disku. Pokud databázi zálohujete, uvolní se volné místo, které lze znovu využít. Správce databáze musí naplánovat zálohování transakčního protokolu, aby se velikost souborů protokolu zmenšila na přiměřenou velikost.

Funkce automatického zmenšování SQL Serveru je ve výchozím nastavení u databází instance SQL Serveru vypnuta. Ve scénáři, kdy máte několik relativně malých databází, které se zvětšují buď vkládáním nových tuplů, nebo velkým počtem prázdných míst vzniklých odstraněním několika tuplů, se v této situaci stává funkce SQL Server Auto Shrink docela užitečnou. Navíc se nemusíte obávat fragmentace velikosti databázových souborů.

Při zmenšování databáze musíte v rámci údržby brát v úvahu operace zmenšování jak kritických, tak i relativně větších databází. Kromě toho by se člověk měl vyhnout spouštění funkce ručního zmenšování; důsledkem tohoto postupu je, že se nikdy neseznámí s problémy týkajícími se nového nebo existujícího požadavku. Zmenšování transakčních souborů je však lepší než zmenšování datových souborů.

Způsob aktivace a deaktivace automatického zmenšování databáze SQL Serveru?

Uživatelé mohou aktivovat a deaktivovat možnost automatického zmenšování databáze pomocí SSMS a T-SQL oběma způsoby.

Aktivace automatického zmenšování databáze pomocí SSMS:

Uživatelé mohou tuto možnost povolit nebo zakázat ve vlastnostech databáze s označením Auto Shrink. Zde hodnota True v rozevíracím seznamu tuto možnost pro databázi povolí.

Povolení automatického zmenšování databáze pomocí jazyka T-SQL:

Uživatelé mohou provést níže uvedené příkazy jazyka T-SQL a povolit nebo zakázat zmenšování databáze. Pro výše uvedený příkaz T-SQL jsme použili databázi AdventureWorks.

Vliv zmenšování databáze na výkon dotazu

Na straně výkonu dotazu se mohou projevit nepříznivé účinky, pokud pro databázi zapnete možnost automatického zmenšování a nastavení automatického růstu společně. Můžeme si ponechat určitý prostor pro růst datových souborů a také se vyhnout některým častým událostem automatického růstu tím, že nastavíme velikost databáze na optimální hodnotu nebo většinou každá databáze má některé parametry se zapnutými funkcemi automatického růstu.

U takových databází musíme funkce automatického zmenšování aktivovat, když je databáze menší, jedna a žádná další operace CRUD, tak může umožnit zmenšení datových souborů a získat volné místo, které jsme cíleně poskytli, aby se naše databáze vyhnula událostem automatického růstu. Volné místo v datových souborech a souborech protokolu se bude uvolňovat automaticky pravidelně s kruhovou sekvenční prioritou pomocí funkce automatického zmenšování, pokud má tuto funkci zapnutou více databází.

U databází s velkou velikostí se provede automatické zvětšování a následně automatické zmenšování, které povede k problémům s výkonem způsobeným fragmentací na úrovni systému. Když to všechno shrneme, pro žádnou databázi by automatické zmenšování nemělo být aktivováno na základě následujících důvodů:

• Provádění algoritmů automatického smršťování SQL Serveru bez jakéhokoli účelu rozhodně povede k masivnímu plýtvání prostředky
• Ať už provedete automatické smršťování SQL Serveru nebo ruční smršťování, zřejmě dojde k fragmentaci indexů a to nakonec způsobí i smrštění datových souborů
• Pokud server také vynucuje hranice subsystému IO, spuštění smršťování jej může přetlačit, což bude mít za následek dlouhé délky diskových front a případně IO timeouty, to bude masivně spotřebovávat IO a CPU zdroje serveru
• Výkon systému bude zpomalovat fragmentace na úrovni disku souborového systému, což je opět přímý důsledek častého provádění zmenšování a zvětšování datových souborů

Chcete-li porozumět více událostem růstu a zmenšování v SQL Serveru, přečtěte si tento článek: Získejte podrobnosti o událostech růstu a zmenšování databáze SQL Serveru.

V případě kritické databáze by mohl odborník provést ruční operaci zmenšení na úrovni databázového souboru. Činnost ručního zmenšení souboru lze provést, když je provedena operace odstranění a poté bylo místo obnoveno. Po provedení operace zmenšení musíme obnovit fragmentované indexy, protože operace zmenšení může vést k fragmentaci indexů. Procento fragmentace indexů by mohl uživatel kontrolovat pomocí příkazů T-SQL DMV. Smršťování souboru protokolu by však mělo být prováděno ručně podle potřeby a nemělo by být součástí pravidelné činnosti údržby.

Pro pravidelné sledování využití disku by uživatel mohl před provedením ruční operace smršťování analyzovat hlášení o využití disku pomocí SSMS, které na displeji poskytuje přehled o informacích o prostoru pro data a protokoly databáze. Pokud by uživatel chtěl získat přehled pro výpočet rezervovaného a volného místa pro databázové soubory, pak se zpráva o využití disku stane velmi užitečnou. Disková sestava však obývá informace pomocí DMV SQL Serveru. Disková sestava je k dispozici v níže uvedeném adresáři pomocí SSMS.

Databáze >> Sestavy >> Standardní sestavy >> Využití disku

Zde máme diskovou sestavu pro databázi. Uživatel si mohl prohlédnout statistiky datových souborů a souborů protokolu v reálném čase. Tento report zahrnuje primární informace o celkovém rezervovaném prostoru, rezervovaném prostoru pro datové soubory, rezervovaném prostoru pro transakční protokoly a rezervovaném prostoru pro OLTP v paměti.

Smršťování databáze automaticky využívá úlohy SQL Serveru

Takové skripty by mohl uživatel provést naplánováním úlohy pro provedení operace smršťování databáze s činností plánovače. Jako první krok skriptu se zjistí volné místo v databázovém souboru a poté se tento soubor zmenší, pokud vyhoví definovaným kritériím pro volné místo. Volné místo by mělo být vypočteno pomocí DMV SQL Serveru.

Sledování databázového souboru provádí v podstatě správce databáze, který následně provádí kroky v databázi, aby sledoval velikost databázového souboru. Pokud je předdefinovaná hranice překročena nějakým souborem, musí tyto kroky provést. Tuto činnost lze tedy automatizovat pomocí úlohy SQL Serveru, která se bude provádět denně, týdně nebo měsíčně v předem definovaný čas.

Nejlepší praxí je naplánovat tuto úlohu pouze na soubor protokolu databáze a volné místo v datovém souboru sledovat ručně. Může totiž ovlivnit i výkonnost dotazů. SQL Server DMV vrátí datový soubor a soubor protokolu ve stejné sadě výsledků, takže typ souboru je třeba v příkazu T-SQL SQL Server DMV rozdělit. Před zmenšením souboru může uživatel použít logiku dotazu na celkové místo a volné místo souboru protokolu. Pokud se kritéria výpočtu shodují s vlastností souboru, pak se soubor zmenší s cílovou velikostí souboru.

Kontrola volného místa pro databázové soubory:

Pokud se taková kritéria shodují s databázovými soubory, například pokud je volné místo větší než (n) MB/GB, pokud n(%) volného místa porovnat s celkovým místem a mnoho dalších. Pokud pro soubor protokolu není k dispozici volné místo a velikost souboru se blíží přibližně hodnotě parametru maximální velikosti souboru, musí správce databáze provést průzkum v protokolu transakcí.

Zmenšit soubor databáze:

Zde 5120 je cílová velikost souboru v MB. Soubor tedy bude mít velikost 5120 MB. Nastavte dotaz T-SQL s požadovanými kritérii v kroku úlohy SQL Serveru a naplánujte jej na vypnuté hodiny.

Možnost automatického zmenšení SQL Serveru nelze povolit pro všechny databáze. Konkrétně pomáhá u menších databází, které provádějí srovnatelně méně operací CRUD.

Závěr

V tomto článku jsme probrali vlastnost Auto Shrink databáze v SQL Serveru pro zmenšení datových souborů a souborů protokolu a odstranění nevyužitého místa. Zmenšení databáze je nákladná operace a měla by se používat opatrně.

• Autor
• Poslední příspěvky

Jignesh má dobré zkušenosti v oblasti databázových řešení a architektury, spolupracuje s mnoha zákazníky na návrhu &architektury databází, vývoji SQL, administraci, optimalizaci dotazů, ladění výkonu, HA a zotavení po havárii.
Zobrazit všechny příspěvky od Jignesh Raiyani

Nejnovější příspěvky od Jignesh Raiyani (zobrazit všechny)

• Page Life Expectancy (PLE) in SQL Server – July 17, 2020
• Jak automatizovat rozdělování tabulek v SQL Serveru – 7. července 2020
• Konfigurace skupin dostupnosti SQL Serveru Always On na AWS EC2 – 6. července 2020