{"id":556,"date":"2011-12-13T11:39:22","date_gmt":"2011-12-13T16:39:22","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=556"},"modified":"2015-01-02T12:11:02","modified_gmt":"2015-01-02T17:11:02","slug":"t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/","title":{"rendered":"T-SQL Tuesday #25 \u2013 SQL Server Tips &#038; Tricks"},"content":{"rendered":"<style type=\"text\/css\">\n<!--\np {margin-top:8px;}\n-->\n<\/style>\n<p><\/p>\n<p><a href=\"http:\/\/sqlblog.com\/blogs\/allen_white\/archive\/2011\/12\/05\/t-sql-tuesday-025-invitation-to-share-your-tricks.aspx\"><img loading=\"lazy\" decoding=\"async\" height=\"132\" border=\"0\" hspace=\"9\" width=\"131\" alt=\"T-SQL Tuesday #25\" src=\"http:\/\/images.sqlity.net\/SqlTuesday.png\" \/><\/a><\/p>\n<p>T-SQL Tuesday #25 is hosted by Allen White (<a href=\"http:\/\/sqlblog.com\/blogs\/allen_white\/default.aspx\">blog<\/a>). This month\u2019s topic is \u201c<a href=\"http:\/\/sqlblog.com\/blogs\/allen_white\/archive\/2011\/12\/05\/t-sql-tuesday-025-invitation-to-share-your-tricks.aspx\">SQL Server Tips and Tricks<\/a>\u201d.<\/p>\n<h2>Transaction Log Reuse Wait<\/h2>\n<p>\nOne question that keeps coming up in forums, at user group meetings and on twitter is: Why is my transaction log growing out of bounds?\n<\/p>\n<p>\nTo adhere to the <a href=\"http:\/\/en.wikipedia.org\/wiki\/ACID\">ACID properties<\/a>, SQL Server records all changes to the database first into the transaction log file. A transaction can only be committed after that write succeeds. The changes to the data itself actually get applied only to the data pages in the buffer pool. If and when they get written to the disk is influenced by several factors that are independent of the transaction itself. The records in the transaction log allow SQL Server to redo a change after a crash for example that prevented the data page changes to be written to disk.\n<\/p>\n<p>\nThat means in theory, that after the transaction is committed and after the data pages are preserved on disk, there is no need for SQL Server to hold on to the transaction log data anymore. That's why the transaction log usually does not grow unbounded, because SQL Server can reuse the parts of the file(s) that are not needed anymore.\n<\/p>\n<p>\nIn practice however, there can be several reasons, why such a reuse is not possible. The most common one is the requirement for log backups. If you have your database set to recovery mode FULL, SQL Server does not reuse any part of the log file until it is backed up with a <a href=\"http:\/\/sqlserverpedia.com\/wiki\/Types_of_Backups\">transaction log backup<\/a>. There are several other reasons why SQL Server might have to wait before it can reuse the transaction log, ranging from long running open transactions to transactional replication.\n<\/p>\n<p>\nIf you are in a situation that requires you to find out why the transaction log file keeps growing and growing, this simple query can give you the answer:\n<\/p>\n[sql]\nSELECT name,log_reuse_wait_desc FROM sys.databases;<br \/>\n[\/sql] <\/p>\n<p>\nThe log_reuse_wait_desc column contains the reason why the SQL Server currently can't reuse the log file of that database. For an explanation of each of the eight reasons, check out my new article series here:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/en\/1805\/eight-reasons-transaction-log-files-keep-growing\/\">Eight Reasons why your Transaction Log Files keep growing<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>T-SQL Tuesday #25 is hosted by Allen White (blog). This month\u2019s topic is \u201cSQL Server Tips and Tricks\u201d. Transaction Log Reuse Wait One question that keeps coming up in forums, at user group meetings and on twitter is: Why is <a href=\"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2560,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,13],"tags":[],"class_list":["post-556","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","category-t-sql-tuesday"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>T-SQL Tuesday #25 \u2013 SQL Server Tips &amp; Tricks - sqlity.net<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-\u2013-sql-server-tips-tricks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"T-SQL Tuesday #25 \u2013 SQL Server Tips &amp; Tricks - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"T-SQL Tuesday #25 is hosted by Allen White (blog). This month\u2019s topic is \u201cSQL Server Tips and Tricks\u201d. Transaction Log Reuse Wait One question that keeps coming up in forums, at user group meetings and on twitter is: Why is [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-\u2013-sql-server-tips-tricks\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2011-12-13T16:39:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-01-02T17:11:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png\" \/>\n\t<meta property=\"og:image:width\" content=\"129\" \/>\n\t<meta property=\"og:image:height\" content=\"130\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"T-SQL Tuesday #25 \u2013 SQL Server Tips &#038; Tricks\",\"datePublished\":\"2011-12-13T16:39:22+00:00\",\"dateModified\":\"2015-01-02T17:11:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/\"},\"wordCount\":383,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/tsql2sday.png\",\"articleSection\":[\"General\",\"T-SQL Tuesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/\",\"name\":\"T-SQL Tuesday #25 \u2013 SQL Server Tips & Tricks - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/tsql2sday.png\",\"datePublished\":\"2011-12-13T16:39:22+00:00\",\"dateModified\":\"2015-01-02T17:11:02+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/tsql2sday.png\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/tsql2sday.png\",\"width\":129,\"height\":130,\"caption\":\"T-SQL Tuesday 58: Passwords - #tsql2sday\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/556\\\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"T-SQL Tuesday #25 \u2013 SQL Server Tips &#038; Tricks\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"T-SQL Tuesday #25 \u2013 SQL Server Tips & Tricks - sqlity.net","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-\u2013-sql-server-tips-tricks\/","og_locale":"en_US","og_type":"article","og_title":"T-SQL Tuesday #25 \u2013 SQL Server Tips & Tricks - sqlity.net","og_description":"T-SQL Tuesday #25 is hosted by Allen White (blog). This month\u2019s topic is \u201cSQL Server Tips and Tricks\u201d. Transaction Log Reuse Wait One question that keeps coming up in forums, at user group meetings and on twitter is: Why is [more...]","og_url":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-\u2013-sql-server-tips-tricks\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2011-12-13T16:39:22+00:00","article_modified_time":"2015-01-02T17:11:02+00:00","og_image":[{"width":129,"height":130,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","type":"image\/png"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"T-SQL Tuesday #25 \u2013 SQL Server Tips &#038; Tricks","datePublished":"2011-12-13T16:39:22+00:00","dateModified":"2015-01-02T17:11:02+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/"},"wordCount":383,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","articleSection":["General","T-SQL Tuesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/","url":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/","name":"T-SQL Tuesday #25 \u2013 SQL Server Tips & Tricks - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","datePublished":"2011-12-13T16:39:22+00:00","dateModified":"2015-01-02T17:11:02+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","width":129,"height":130,"caption":"T-SQL Tuesday 58: Passwords - #tsql2sday"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/556\/t-sql-tuesday-25-%e2%80%93-sql-server-tips-tricks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"T-SQL Tuesday #25 \u2013 SQL Server Tips &#038; Tricks"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-8Y","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/09\/tsql2sday.png","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=556"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/556\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2560"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=556"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}