{"id":1545,"date":"2025-07-20T07:16:50","date_gmt":"2025-07-20T07:16:50","guid":{"rendered":"https:\/\/virtualall.sk\/?p=1545"},"modified":"2026-01-11T11:10:04","modified_gmt":"2026-01-11T11:10:04","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":"<p><strong>MSSQL transaction logs<\/strong> are a key element of the database system that can quickly fill up disk space if not properly managed. In this article, I will show you how to effectively manage <strong>MSSQL transaction logs<\/strong> and prevent disk space issues.<\/p>\n<p>The .ldf file can grow to hundreds of GB if <strong>MSSQL transaction logs<\/strong> are not regularly backed up or truncated.<\/p>\n<h2>Recovery Models for MSSQL Transaction Logs<\/h2>\n<h3>FULL Recovery Model<\/h3>\n<ul>\n<li>Retains every transaction until log backup<\/li>\n<li>Enables point-in-time recovery<\/li>\n<li>Requires regular log backups<\/li>\n<\/ul>\n<h3>SIMPLE Recovery Model<\/h3>\n<ul>\n<li>Automatically truncates logs after checkpoint<\/li>\n<li>Recovery only to last full backup<\/li>\n<li>No log backups required<\/li>\n<\/ul>\n<h3>BULK_LOGGED Recovery Model<\/h3>\n<ul>\n<li>Hybrid approach for bulk operations<\/li>\n<li>Suitable for ETL processes<\/li>\n<\/ul>\n<p>More about recovery models in <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/recovery-models-sql-server\" target=\"_blank\" rel=\"dofollow noopener\">Microsoft SQL Server documentation<\/a>.<\/p>\n<h2>SQL Commands for Managing MSSQL Transaction Logs<\/h2>\n<h3>View Recovery Models<\/h3>\n<pre><code>SELECT name AS Database, recovery_model_desc AS Mode\nFROM sys.databases\nWHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');<\/code><\/pre>\n<h3>Change Recovery Model and Shrink Log<\/h3>\n<pre><code>ALTER DATABASE [dbname] SET RECOVERY SIMPLE\nDBCC SHRINKFILE ([logfile], 100)<\/code><\/pre>\n<h3>Backup Transaction Log<\/h3>\n<pre><code>BACKUP LOG [database] TO DISK = 'C:\\Backups\\db_log.trn'<\/code><\/pre>\n<h2>Recommendations for MSSQL Transaction Logs<\/h2>\n<table style=\"width:100%; border-collapse: collapse; margin: 20px 0;\">\n<tr style=\"background-color: #00b336; color: white;\">\n<th style=\"padding: 12px; border: 1px solid #ddd;\">Environment<\/th>\n<th style=\"padding: 12px; border: 1px solid #ddd;\">Recovery Model<\/th>\n<\/tr>\n<tr>\n<td style=\"padding: 12px; border: 1px solid #ddd;\">Production systems<\/td>\n<td style=\"padding: 12px; border: 1px solid #ddd;\">FULL + regular log backups<\/td>\n<\/tr>\n<tr style=\"background-color: #f9f9f9;\">\n<td style=\"padding: 12px; border: 1px solid #ddd;\">Dev\/Test<\/td>\n<td style=\"padding: 12px; border: 1px solid #ddd;\">SIMPLE<\/td>\n<\/tr>\n<tr>\n<td style=\"padding: 12px; border: 1px solid #ddd;\">Reporting databases<\/td>\n<td style=\"padding: 12px; border: 1px solid #ddd;\">SIMPLE<\/td>\n<\/tr>\n<\/table>\n<h2>Summary<\/h2>\n<p>Managing <strong>MSSQL transaction logs<\/strong> is critical for stability. Key points:<\/p>\n<ul>\n<li>FULL model requires regular log backups<\/li>\n<li>SIMPLE model automatically truncates logs<\/li>\n<li>Improper configuration leads to disk filling<\/li>\n<\/ul>\n<p>More guides on <a href=\"https:\/\/virtualall.sk\/category\/azure\/\">Azure and databases<\/a> in our section.<\/p>\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":4,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts\/1545\/revisions"}],"predecessor-version":[{"id":1933,"href":"https:\/\/virtualall.sk\/en\/wp-json\/wp\/v2\/posts\/1545\/revisions\/1933"}],"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}]}}