{"id":2513,"date":"2024-10-04T23:00:30","date_gmt":"2024-10-04T23:00:30","guid":{"rendered":"https:\/\/galhano.com\/blog\/?p=2513"},"modified":"2026-01-20T10:39:17","modified_gmt":"2026-01-20T10:39:17","slug":"sql-server-log-file-size-and-free-up-space","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=2513","title":{"rendered":"sql server log file size and free up space"},"content":{"rendered":"\n<p>This should help reduce the log file size and free up space. If there are pending transactions or the log is in FULL mode, the log should be backed up before performing shrink.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DBCC SQLPERF(LOGSPACE);  --  Checks the space used by the log\nDBCC OPENTRAN (&#039;mydb&#039;);  -- Checks for open transactions in the database\nBACKUP LOG mydb TO DISK = &#039;C:\\_Backup\\mydb_log.bak&#039;;  --  Backs up the log (required in FULL mode)\nDBCC SHRINKFILE (N&#039;mydb_log&#039;, TRUNCATEONLY);  -- Shrinks the log file, freeing up unused space\n<\/code><\/pre>\n\n\n\n<p>Stop backup log:<\/p>\n\n\n\n<p>SELECT session_id, command, status, start_time FROM sys.dm_exec_requests WHERE command = &#8216;BACKUP DATABASE&#8217; OR command = &#8216;BACKUP LOG&#8217;;<\/p>\n\n\n\n<p>KILL sesionid;<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This should help reduce the log file size and free up space. If there are pending transactions or the log is in FULL mode, the log should be backed up before performing shrink. Stop backup log: SELECT session_id, command, status, start_time FROM sys.dm_exec_requests WHERE command = &#8216;BACKUP DATABASE&#8217; OR command = &#8216;BACKUP LOG&#8217;; KILL sesionid;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,9,104],"tags":[],"class_list":["post-2513","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql-server","category-transact-sql","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2513"}],"version-history":[{"count":2,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2513\/revisions"}],"predecessor-version":[{"id":2516,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2513\/revisions\/2516"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2513"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}