{"id":890,"date":"2013-12-26T06:28:05","date_gmt":"2013-12-26T06:28:05","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=890"},"modified":"2024-04-26T12:48:06","modified_gmt":"2024-04-26T12:48:06","slug":"diagnosing-and-rectifying-sql-transaction-log-growth","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/diagnosing-and-rectifying-sql-transaction-log-growth\/","title":{"rendered":"Diagnosing and rectifying SQL Transaction Log Growth"},"content":{"rendered":"<h4>Diagnosing and rectifying SQL Transaction Log Growth<\/h4>\n<p>Ever wonder why your transaction logs can grow out of control? First place to check is whether the Database is set to Full or Simple mode. \u00a0 Unless you are running in Simple Mode, backups need to be taken at least hourly to reduce the accumulating Transaction Logs.<\/p>\n<p>The database keeps its own sys log that can tell you what\u2019s preventing Transaction Logs from being cleared; here\u2019s a query to run:<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre lang=\"php\">  SELECT TOP 1000 [name]\r\n ,[log_reuse_wait]\r\n ,[log_reuse_wait_desc]\r\n ,[is_date_correlation_on]\r\n \r\n FROM [master].[sys].[databases]<\/pre>\n<p>First thing to try is to Shrink the database, assuming you are already in Simple Recovery Mode:<\/p>\n<pre lang=\"php\"> USE dbname\r\nCHECKPOINT\r\n--First parameter below is the fileno for the log file, often 2. Check the sys.database_files\r\n--Second parameter is the target size in MB.\r\nDBCC SHRINKFILE(2, 500)\r\nDBCC SQLPERF(LOGSPACE)\r\nDBCC LOGINFO\r\n\r\n<\/pre>\n<p>Logfile space is not automatically recovered. \u00a0The SQL to Truncate and recover the log space is to run the following against the LDF file:<\/p>\n<pre lang=\"php\"> DBCC SHRINKFILE\r\n(\r\nlogical file_name\r\ntarget_size in MB ], TRUNCATEONLY\r\n)\r\nWITH NO_INFOMSGS<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Diagnosing and rectifying SQL Transaction Log Growth Ever wonder why your transaction logs can grow out of control? First place to check is whether the Database is set to Full or Simple mode. \u00a0 Unless you are running in Simple Mode, backups need to be taken at least hourly to reduce the accumulating Transaction Logs. [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":941,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[35],"tags":[],"class_list":["post-890","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=890"}],"version-history":[{"count":2,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/890\/revisions"}],"predecessor-version":[{"id":943,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/890\/revisions\/943"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/941"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}