{"id":1545,"date":"2025-07-20T07:16:50","date_gmt":"2025-07-20T07:16:50","guid":{"rendered":"https:\/\/virtualall.sk\/?p=1545"},"modified":"2026-09-11T14:45:08","modified_gmt":"2026-09-11T14:45:08","slug":"mssql-transakcne-logy-neviditelny-pozierac-diskoveho-priestoru","status":"publish","type":"post","link":"https:\/\/virtualall.sk\/en\/2025\/07\/mssql-transakcne-logy-neviditelny-pozierac-diskoveho-priestoru\/","title":{"rendered":"MSSQL Transaction Logs &#8211; Disk Space Management"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Transak\u010dn\u00e9 logy v SQL Serveri s\u00fa k\u013e\u00fa\u010dov\u00fdm prvkom datab\u00e1zov\u00e9ho syst\u00e9mu, no ich v\u00fdznam a spr\u00e1va b\u00fdva \u010dasto podce\u0148ovan\u00e1. Ka\u017ed\u00e1 datab\u00e1za v SQL Serveri m\u00e1 svoj logick\u00fd z\u00e1znamov\u00fd s\u00fabor (s pr\u00edponou <code>.ldf<\/code>), ktor\u00fd uchov\u00e1va inform\u00e1cie o v\u0161etk\u00fdch zmen\u00e1ch vykonan\u00fdch nad datab\u00e1zou vr\u00e1tane vkladania, \u00faprav a mazania \u00fadajov. Tieto logy sl\u00fa\u017eia prim\u00e1rne na zabezpe\u010denie konzistencie d\u00e1t a umo\u017e\u0148uj\u00fa tzv. &#8220;point-in-time recovery&#8221;, teda obnovenie datab\u00e1zy do konkr\u00e9tneho \u010dasov\u00e9ho bodu v pr\u00edpade hav\u00e1rie.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Znie to u\u017eito\u010dne a aj je. Probl\u00e9m nast\u00e1va vtedy, ke\u010f tieto logy <strong>nikto nez\u00e1lohuje, nesleduje alebo ich neobmedz\u00ed<\/strong>. Bez \u00fadr\u017eby a spr\u00e1vneho nastavenia recovery modelu m\u00f4\u017ee log narasta\u0165 prakticky donekone\u010dna. V produk\u010dn\u00fdch prostrediach, kde be\u017eia ve\u013ek\u00e9 importy, ETL \u00falohy alebo hromadn\u00e9 oper\u00e1cie, sa ve\u013ekos\u0165 transak\u010dn\u00e9ho logu m\u00f4\u017ee vy\u0161plha\u0165 na <strong>stovky gigabajtov, ba dokonca nieko\u013eko terabajtov<\/strong>, \u010d\u00edm zahlcuje diskov\u00fd priestor a zni\u017euje v\u00fdkon syst\u00e9mu.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Na\u0161\u0165astie existuj\u00fa rie\u0161enia. SQL Server pon\u00faka r\u00f4zne re\u017eimy obnovy (<code>FULL<\/code>, <code>SIMPLE<\/code>, <code>BULK_LOGGED<\/code>), z ktor\u00fdch ka\u017ed\u00fd m\u00e1 vplyv na spr\u00e1vanie transak\u010dn\u00fdch logov. V pr\u00edpade, \u017ee nevyu\u017e\u00edvate mo\u017enos\u0165 \u010dasov\u00e9ho obnovenia datab\u00e1zy, m\u00f4\u017eete logovanie obmedzi\u0165 prechodom do <code>SIMPLE<\/code> recovery modelu. Alternat\u00edvne, ak zostanete pri <code>FULL<\/code>, sta\u010d\u00ed pravidelne z\u00e1lohova\u0165 logy \u010d\u00edm sa ich ve\u013ekos\u0165 prirodzene obmedz\u00ed. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Recovery modely v SQL Serveri: \u010co ur\u010duj\u00fa a pre\u010do na nich z\u00e1le\u017e\u00ed<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server podporuje tri hlavn\u00e9 recovery modely: <strong>FULL<\/strong>, <strong>SIMPLE<\/strong> a <strong>BULK_LOGGED<\/strong>. Tieto re\u017eimy ur\u010duj\u00fa, ako SQL Server zaznamen\u00e1va transakcie do logov a ak\u00e9 mo\u017enosti obnovy datab\u00e1zy poskytuje.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">1. <strong>FULL recovery model<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Tento re\u017eim uchov\u00e1va ka\u017ed\u00fa transakciu a\u017e dovtedy, k\u00fdm nie je vykonan\u00e1 z\u00e1loha logu. Poskytuje najvy\u0161\u0161iu \u00farove\u0148 ochrany d\u00e1t umo\u017e\u0148uje obnovi\u0165 datab\u00e1zu <strong>na presn\u00fd \u010dasov\u00fd bod<\/strong>. Je nevyhnutn\u00fd v produk\u010dn\u00fdch prostrediach, kde je kritick\u00e1 kontinuita prev\u00e1dzky.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Nev\u00fdhoda:<\/strong><br>Ak sa pravidelne nez\u00e1lohuj\u00fa transak\u010dn\u00e9 logy, <code>.ldf<\/code> s\u00fabor rastie donekone\u010dna, \u010do vedie k zahlteniu disku.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">2. <strong>SIMPLE recovery model<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Transak\u010dn\u00e9 logy s\u00fa priebe\u017ene orez\u00e1van\u00e9 po skon\u010den\u00ed transakcie a checkpointu sa uvo\u013en\u00ed ich miesto. Obnova datab\u00e1zy je mo\u017en\u00e1 len na posledn\u00fd pln\u00fd backup, <strong>nie na konkr\u00e9tny \u010das<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>V\u00fdhoda:<\/strong><br>Nepotrebuje\u0161 z\u00e1lohova\u0165 logy, logy nezaberaj\u00fa ve\u013ea miesta.<br><strong>Nev\u00fdhoda:<\/strong><br>Nie je mo\u017en\u00e1 &#8220;point-in-time&#8221; obnova.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">3. <strong>BULK_LOGGED recovery model<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Ide o kompromis medzi FULL a SIMPLE. Pri hromadn\u00fdch oper\u00e1ci\u00e1ch (napr. <code>BULK INSERT<\/code>, <code>SELECT INTO<\/code>) sa pou\u017e\u00edva minimalizovan\u00e9 logovanie. M\u00f4\u017ee sa pou\u017ei\u0165 pre v\u00fdkon po\u010das ve\u013ek\u00fdch d\u00e1vok, ale st\u00e1le vy\u017eaduje z\u00e1lohovanie logov.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83e\udde0 <strong>Ktor\u00fd recovery model pou\u017ei\u0165?<\/strong><\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Scen\u00e1r<\/th><th>Odpor\u00fa\u010dan\u00fd recovery model<\/th><\/tr><\/thead><tbody><tr><td>Produk\u010dn\u00fd syst\u00e9m s kritick\u00fdmi d\u00e1tami<\/td><td>FULL<\/td><\/tr><tr><td>V\u00fdvojov\u00e9\/testovacie prostredie<\/td><td>SIMPLE<\/td><\/tr><tr><td>ETL server po\u010das d\u00e1vkov\u00e9ho spracovania<\/td><td>BULK_LOGGED alebo SIMPLE<\/td><\/tr><tr><td>Reportingov\u00e9 datab\u00e1zy<\/td><td>SIMPLE<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Ke\u010f logy prerast\u00fa syst\u00e9m: praktick\u00fd pr\u00edklad<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"934\" height=\"907\" src=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-125.png\" alt=\"\ud83e\udde0 Ktor\u00fd recovery model pou\u017ei\u0165?\" class=\"wp-image-1547\" srcset=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-125.png 934w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-125-300x291.png 300w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-125-768x746.png 768w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-125-12x12.png 12w\" sizes=\"auto, (max-width: 934px) 100vw, 934px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Rie\u0161enie: ako zmen\u0161i\u0165 logy a zabr\u00e1ni\u0165 ich rastu<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Pred akouko\u013evek zmenou je dobr\u00e9 ma\u0165 preh\u013ead o tom, v akom re\u017eime s\u00fa va\u0161e datab\u00e1zy. Spus\u0165 si tento jednoduch\u00fd dotaz:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name AS Databaza, recovery_model_desc AS Rezim\nFROM sys.databases\nWHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');<\/code><\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"487\" height=\"178\" src=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-126.png\" alt=\"Rie\u0161enie: ako zmen\u0161i\u0165 logy a zabr\u00e1ni\u0165 ich rastu\" class=\"wp-image-1548\" srcset=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-126.png 487w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-126-300x110.png 300w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-126-18x7.png 18w\" sizes=\"auto, (max-width: 487px) 100vw, 487px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\"><strong>Automatick\u00e9 rie\u0161enie: Skript na prepnutie recovery re\u017eimu a zmen\u0161enie logu<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Tento skript:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>N\u00e1jde log s\u00fabor a pok\u00fasi sa ho zmen\u0161i\u0165 na 100 MB.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Presko\u010d\u00ed syst\u00e9mov\u00e9 datab\u00e1zy.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Pre ka\u017ed\u00fa datab\u00e1zu nastav\u00ed <code>SIMPLE<\/code> recovery re\u017eim.<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @dbname NVARCHAR(128)\nDECLARE @logfile NVARCHAR(128)\nDECLARE @sql NVARCHAR(MAX)\n\nDECLARE db_cursor CURSOR FOR\nSELECT name FROM sys.databases\nWHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')\n\nOPEN db_cursor\nFETCH NEXT FROM db_cursor INTO @dbname\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    PRINT '---- Spracovavam databazu: ' + @dbname\n\n    -- Nastavenie SIMPLE recovery modelu\n    SET @sql = 'ALTER DATABASE &#91;' + @dbname + '] SET RECOVERY SIMPLE WITH NO_WAIT'\n    EXEC (@sql)\n\n    -- Ziskanie nazvu LOG suboru\n    SET @sql = '\n        SELECT @logfile_out = name \n        FROM &#91;' + @dbname + '].sys.database_files \n        WHERE type_desc = ''LOG'''\n    \n    EXEC sp_executesql @sql, N'@logfile_out NVARCHAR(128) OUTPUT', @logfile_out=@logfile OUTPUT\n\n    IF @logfile IS NOT NULL\n    BEGIN\n        PRINT '  \u2192 Zmensujem logovy subor: ' + @logfile\n\n        SET @sql = 'USE &#91;' + @dbname + ']; DBCC SHRINKFILE (&#91;' + @logfile + '], 100)'\n        EXEC (@sql)\n    END\n    ELSE\n    BEGIN\n        PRINT '  \u00d7 Nepodarilo sa ziskat logovy subor pre databazu: ' + @dbname\n    END\n\n    FETCH NEXT FROM db_cursor INTO @dbname\nEND\n\nCLOSE db_cursor\nDEALLOCATE db_cursor\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Odpor\u00fa\u010dania<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Produk\u010dn\u00e9 datab\u00e1zy<\/strong> ponechaj v <code>FULL<\/code> re\u017eime, ale zabezpe\u010d <strong>pravideln\u00e9 z\u00e1lohovanie logov<\/strong>.<\/li>\n\n\n\n<li><strong>V\u00fdvojov\u00e9, testovacie a reportingov\u00e9 datab\u00e1zy<\/strong> nastav na <code>SIMPLE<\/code> re\u017eim.<\/li>\n\n\n\n<li><strong>Automatizuj z\u00e1lohovanie logov<\/strong> cez SQL Agent alebo PowerShell (napr. ka\u017ed\u00fa hodinu).<\/li>\n\n\n\n<li><strong>Logy shrinkuj v\u00fdnimo\u010dne<\/strong>, iba ak nar\u00e1stli abnorm\u00e1lne \u2013 pravideln\u00e9 shrinkovanie zni\u017euje v\u00fdkon.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Nastavenie maxim\u00e1lnej ve\u013ekosti transak\u010dn\u00e9ho logu je mo\u017en\u00e9 jednoducho vykona\u0165 cez SSMS GUI, kde pre ka\u017ed\u00fa datab\u00e1zu zvl\u00e1\u0161\u0165 nastav\u00ed\u0161, ko\u013eko GB m\u00f4\u017ee log zabera\u0165. Odpor\u00fa\u010da sa nastavi\u0165 realistick\u00fa hranicu pod\u013ea charakteru datab\u00e1zy \u2013 pre men\u0161ie datab\u00e1zy napr\u00edklad 1\u20132 GB, pre v\u00e4\u010d\u0161ie produk\u010dn\u00e9 datab\u00e1zy pod\u013ea odhadovan\u00e9ho objemu transakci\u00ed. D\u00f4le\u017eit\u00e9 je z\u00e1rove\u0148 zabezpe\u010di\u0165 pravideln\u00e9 z\u00e1lohovanie logov, inak m\u00f4\u017ee log r\u00fdchlo narazi\u0165 na svoj limit a sp\u00f4sobi\u0165 v\u00fdpadok.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"709\" height=\"662\" src=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-127.png\" alt=\"Odpor\u00fa\u010dania\" class=\"wp-image-1550\" srcset=\"https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-127.png 709w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-127-300x280.png 300w, https:\/\/virtualall.sk\/wp-content\/uploads\/2025\/07\/obrazek-127-13x12.png 13w\" sizes=\"auto, (max-width: 709px) 100vw, 709px\" \/><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">S\u00favisiace n\u00e1vody<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n\n<li><a href=\"https:\/\/virtualall.sk\/2023\/12\/windows-server-rdp-brute-force-ochrana\/\">Windows Server RDP Brute Force ochrana<\/a><\/li>\n\n\n<li><a href=\"https:\/\/virtualall.sk\/2025\/01\/kompletny-sprievodca-zabezpecenim-windows-servera-s-veeam-automatizacia-podla-cis-standardov\/\">Zabezpe\u010denie Windows Servera s Veeam<\/a><\/li>\n\n\n<li><a href=\"https:\/\/virtualall.sk\/2025\/03\/cis-benchmark\/\">CIS Benchmark<\/a><\/li>\n\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Transak\u010dn\u00e9 logy v Microsoft SQL Serveri predstavuj\u00fa nevyhnutn\u00fa s\u00fa\u010das\u0165 fungovania datab\u00e1z, no ich spr\u00e1va b\u00fdva \u010dasto zanedban\u00e1. Tieto logy zaznamen\u00e1vaj\u00fa v\u0161etky zmeny v datab\u00e1ze a sl\u00fa\u017eia na zabezpe\u010denie konzistencie d\u00e1t a mo\u017enos\u0165 obnovenia datab\u00e1zy do konkr\u00e9tneho \u010dasov\u00e9ho bodu. Ak v\u0161ak datab\u00e1za pou\u017e\u00edva re\u017eim FULL a logy sa nez\u00e1lohuj\u00fa, .ldf s\u00fabor m\u00f4\u017ee nar\u00e1s\u0165 do extr\u00e9mnych rozmerov a zaplni\u0165 dostupn\u00e9 miesto na disku. D\u00f4sledkom m\u00f4\u017eu by\u0165 v\u00fdpadky, spomalenie syst\u00e9mu a ohrozenie dostupnosti slu\u017eieb. Rie\u0161en\u00edm je bu\u010f prechod do re\u017eimu SIMPLE pre menej kritick\u00e9 datab\u00e1zy, alebo pravideln\u00e9 z\u00e1lohovanie transak\u010dn\u00fdch logov pomocou Veeam alebo SQL Agent jobov. \u010cl\u00e1nok ukazuje, ako nastavi\u0165 automatick\u00e9 \u010distenie logov, ako zmen\u0161i\u0165 nadmerne ve\u013ek\u00e9 s\u00fabory a pre\u010do je spr\u00e1vna spr\u00e1va logov k\u013e\u00fa\u010dov\u00e1 pre dlhodob\u00fa stabilitu datab\u00e1zov\u00e9ho prostredia.<\/p>\n","protected":false},"author":1,"featured_media":1549,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65],"tags":[144,145],"class_list":["post-1545","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-windows","tag-ayure-sql","tag-azure-mssql"],"_links":{"self":[{"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts\/1545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/comments?post=1545"}],"version-history":[{"count":8,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts\/1545\/revisions"}],"predecessor-version":[{"id":2426,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts\/1545\/revisions\/2426"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/media\/1549"}],"wp:attachment":[{"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/media?parent=1545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/categories?post=1545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/tags?post=1545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}