{"id":723,"date":"2010-04-09T06:54:00","date_gmt":"2010-04-09T06:54:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx"},"modified":"2017-08-03T15:45:21","modified_gmt":"2017-08-03T22:45:21","slug":"a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/","title":{"rendered":"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\" target=\"_blank\" rel=\"noopener noreferrer\">Misconceptions<\/a><\/em> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\"><strong>Myth #9:<\/strong> <em>Data file shrink does not affect performance.<\/em><\/p>\n<p style=\"text-align: justify;\"><em><span style=\"font-family: 'book antiqua', palatino; font-size: 300%;\">Hahahahahahahahahahahahahahaha!<\/span><\/em> &lt;snort&gt;<\/p>\n<p style=\"text-align: justify;\">&lt;wipes tears from eyes, attempts to focus on laptop screen, cleans drool from keyboard&gt;<\/p>\n<p style=\"text-align: justify;\"><strong><u><em>FALSE<\/em><\/u><\/strong><\/p>\n<p style=\"text-align: justify;\">The only time a data file shrink won&#8217;t affect performance is if you use the <em>WITH TRUNCATEONLY<\/em> option and there&#8217;s free space at the end of file being shrunk.<\/p>\n<p style=\"text-align: justify;\">Shrink affects performance while it&#8217;s running. It&#8217;s moving tons of data around, generating I\/Os, fully logging everything it does, and burning CPU.<\/p>\n<p style=\"text-align: justify;\">Shrink affects performance after it&#8217;s run. All that log has to be backed up, log shipped, database mirrored, scanned by transactional replication, and so on. And if the data file grows again, the new space has to be zeroed out again (unless you have instant file initialization enabled).<\/p>\n<p style=\"text-align: justify;\">Worst of all, shrink causes massive index fragmentation\u00a0&#8211; which sucks down performance of range scans.<\/p>\n<p style=\"text-align: justify;\">Unfortunately there was never time for me to rewrite the shrink code (I didn&#8217;t write it like that in the first place) so we&#8217;re probably stuck with its massive suckiness (that&#8217;s a technical term) forever.<\/p>\n<p style=\"text-align: justify;\">Check out this blog post where I go into more details and explain an alternative to using shrink: <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-you-should-not-shrink-your-data-files\/\" target=\"_blank\" rel=\"noopener noreferrer\">Why you should not shrink your data files<\/a><\/em>\u00a0and this one for how to manage your data files correctly: <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-data-file-size-management\/\" target=\"_blank\" rel=\"noopener noreferrer\">Importance of data file size management<\/a><\/em>. And this one just because it&#8217;s cool (safe for work): <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/tgif-time-warp\/\" target=\"_blank\" rel=\"noopener noreferrer\">TGIF Time Warp<\/a><\/em>.<\/p>\n<p style=\"text-align: justify;\">And remember:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Data file shrink is evil<\/li>\n<li>Shrinkdatabase is evil-er<\/li>\n<li>Auto-shrink is <strong><u><em>evil-est<\/em><\/u><\/strong><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Shrink &#8211; just say no. With proper education and effective counselling, we can rid the world of this terrible affliction once and for all.<\/p>\n<p style=\"text-align: justify;\">Happy Friday!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #9: Data file shrink does not affect performance. Hahahahahahahahahahahahahahaha! [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,81],"tags":[],"class_list":["post-723","post","type-post","status-publish","format-standard","hentry","category-misconceptions","category-shrink"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - 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\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #9: Data file shrink does not affect performance. Hahahahahahahahahahahahahahaha! [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-09T06:54:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-03T22:45:21+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=\"2 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\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/\",\"name\":\"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-09T06:54:00+00:00\",\"dateModified\":\"2017-08-03T22:45:21+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance\"}]},{\"@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":"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - 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\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #9: Data file shrink does not affect performance. Hahahahahahahahahahahahahahaha! [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-09T06:54:00+00:00","article_modified_time":"2017-08-03T22:45:21+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/","name":"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-09T06:54:00+00:00","dateModified":"2017-08-03T22:45:21+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (9\/30) data file shrink does not affect performance"}]},{"@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\/723","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=723"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/723\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}