{"id":4812,"date":"2017-10-10T08:16:18","date_gmt":"2017-10-10T15:16:18","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4812"},"modified":"2020-11-16T15:07:41","modified_gmt":"2020-11-16T23:07:41","slug":"sqlskills-sql101-should-you-kill-that-long-running-transaction","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/","title":{"rendered":"SQLskills SQL101: Should you kill that long-running transaction?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>As\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sqlskills-sql101-stored-procedures\/\" target=\"_blank\" rel=\"noopener noreferrer\">Kimberly blogged about earlier this year<\/a>, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">One of the problems you may have encountered is a transaction log file growing because of a long-running query.<\/p>\n<p style=\"text-align: justify;\">What do you do?<\/p>\n<p style=\"text-align: justify;\">The temptation is to kill the query, but is that the correct approach? Well, as always, the answer starts with &#8216;it depends&#8217;.<\/p>\n<p>The first thing to consider is whether you&#8217;re using <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/accelerated-database-recovery-concepts?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Accelerated Database Recovery<\/a> in 2019+. If so, the transaction will roll back immediately, so go ahead.<\/p>\n<p>The rest of this post assumes that you&#8217;re NOT using Accelerated Database Recovery.<\/p>\n<p style=\"text-align: justify;\">A long-running query, no matter how much work it&#8217;s done (and hence how many log records it has generated) will prevent the log from clearing, as the log all the way back to the <em>LOP_BEGIN_XACT<\/em> log record of the long-running transaction is required, in case that transaction rolls back. And the log will not be able to clear until (at least) that long-running transaction has committed or finished rolling-back.<\/p>\n<p style=\"text-align: justify;\">You can tell how many log records a transaction has generated using my script <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/script-open-transactions-with-text-and-plans\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>, along with\u00a0the total space taken up in the log by the transaction.<\/p>\n<p style=\"text-align: justify;\">If the long-running query has generated hardly any log records, then killing it will mean that it rolls-back quickly and then hopefully the next log backup (in the full and bulk-logged recovery models) or checkpoint (in the simple recovery model) will allow the log to clear and stop its growth.<\/p>\n<p style=\"text-align: justify;\">However, if the long-running query has generated a *lot* of log records, then it&#8217;s going to take a long time to roll back (as rolling back each log record means generating the &#8216;anti-operation&#8217; for that log record, making the change, and generating *another* log record describing the change). That rollback itself won&#8217;t cause the log to grow any more, as a transaction always reserves free space in the log to allow it to roll back without requiring log growth (my script above also tells you that amount of space). However, as it will take a long time to roll back, other activity in the log from other transactions will likely cause the log to grow more until it&#8217;s finally able to clear.<\/p>\n<p style=\"text-align: justify;\">So it may actually be better to allow a long-running transaction that&#8217;s generated a lot of log records to continue running until it completes. If the time to completion is going to be a lot less than the time to roll back, this could mean less overall extra log growth until you&#8217;re able to finally clear the log, and then potentially resize it back to normal, and continue running.<\/p>\n<p style=\"text-align: justify;\">The trick is knowing what the query is doing and\/or being able to figure out how close to completion it is. You could look at is the <em>logical_reads<\/em> column in the DMV\u00a0<em>sys.dm_exec_requests<\/em> and correlate that with the number of pages in the index or table being scanned, or look at the number of log records generated in the script output and correlate that to the number of records you&#8217;d expect an <em>UPDATE<\/em> statement to perform.<\/p>\n<p style=\"text-align: justify;\"><strong>Bottom line<\/strong>: don&#8217;t always knee-jerk and decide to cancel a problematic, long-running query without thinking about what the effect of that will be.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[108,98],"tags":[],"class_list":["post-4812","post","type-post","status-publish","format-standard","hentry","category-sql101","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal<\/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:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-10T15:16:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-16T23:07:41+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/\",\"name\":\"SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-10-10T15:16:18+00:00\",\"dateModified\":\"2020-11-16T23:07:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Should you kill that long-running transaction?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal","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:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal","og_description":"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/","og_site_name":"Paul S. Randal","article_published_time":"2017-10-10T15:16:18+00:00","article_modified_time":"2020-11-16T23:07:41+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/","name":"SQLskills SQL101: Should you kill that long-running transaction? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-10-10T15:16:18+00:00","dateModified":"2020-11-16T23:07:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-should-you-kill-that-long-running-transaction\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Should you kill that long-running transaction?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4812"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4812\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}