{"id":6,"date":"2016-01-30T15:20:59","date_gmt":"2016-01-30T14:20:59","guid":{"rendered":"http:\/\/fomas.be\/blog\/?p=6"},"modified":"2016-02-01T11:18:17","modified_gmt":"2016-02-01T10:18:17","slug":"shrink-tempdb","status":"publish","type":"post","link":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/","title":{"rendered":"Shrink MS SQL Temp database using T-SQL"},"content":{"rendered":"

Problem<\/strong>:Temp database on SQL has grown to full size of the disk. Usually because of a bug\/error in a scheduled task. There is no need to increase the disk size or replace with another disk. However other files (temp db log, …) are not able to perform well when the disk where they reside is full.<\/p>\n

Shrinking our temp db is the only solution. Best practices tell us to always do this with a restart of the server. Sometimes, this just isn’t an option.<\/p>\n

Solution<\/strong>:<\/p>\n

Got this script from a colleague.<\/p>\n

<\/p>\n

Caution: This will clear a lot of buffers. Temporary performance impact is to be expected.<\/strong><\/p>\n

\n

DBCC FREEPROCCACHE
\nGO
\nDBCC DROPCLEANBUFFERS
\ngo
\nDBCC FREESYSTEMCACHE (‘ALL’)
\nGO
\nDBCC FREESESSIONCACHE
\nGO
\nDBCC SHRINKFILE (TEMPDEV2,10240)
\nGO
\nDBCC SHRINKFILE (TEMPDEV,10240)
\nGO
\nDBCC SHRINKFILE (TEMPDEV3,10240)
\nGO
\nDBCC SHRINKFILE (TEMPDEV4,10240)
\nGO<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Problem:Temp database on SQL has grown to full size of the disk. Usually because of a bug\/error in a scheduled task. There is no need to increase the disk size or replace with another disk. However other files (temp db log, …) are not able to perform well when the disk where they reside is…Read more Shrink MS SQL Temp database using T-SQL<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_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","enabled":false},"version":2}},"categories":[2],"tags":[],"jetpack_publicize_connections":[],"yoast_head":"\nShrink MS SQL Temp database using T-SQL - @F0mix<\/title>\n<meta name=\"description\" content=\"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Shrink MS SQL Temp database using T-SQL - @F0mix\" \/>\n<meta property=\"og:description\" content=\"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running\" \/>\n<meta property=\"og:url\" content=\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/\" \/>\n<meta property=\"og:site_name\" content=\"@F0mix\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-30T14:20:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-02-01T10:18:17+00:00\" \/>\n<meta name=\"author\" content=\"fomas\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"fomas\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/\",\"url\":\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/\",\"name\":\"Shrink MS SQL Temp database using T-SQL - @F0mix\",\"isPartOf\":{\"@id\":\"http:\/\/fomas.be\/blog\/#website\"},\"datePublished\":\"2016-01-30T14:20:59+00:00\",\"dateModified\":\"2016-02-01T10:18:17+00:00\",\"author\":{\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b\"},\"description\":\"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running\",\"breadcrumb\":{\"@id\":\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/fomas.be\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Shrink MS SQL Temp database using T-SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/fomas.be\/blog\/#website\",\"url\":\"http:\/\/fomas.be\/blog\/\",\"name\":\"@F0mix\",\"description\":\"Passion for SQL and POSH\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/fomas.be\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b\",\"name\":\"fomas\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g\",\"caption\":\"fomas\"},\"url\":\"http:\/\/fomas.be\/blog\/author\/fomas\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Shrink MS SQL Temp database using T-SQL - @F0mix","description":"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running","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":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/","og_locale":"en_US","og_type":"article","og_title":"Shrink MS SQL Temp database using T-SQL - @F0mix","og_description":"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running","og_url":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/","og_site_name":"@F0mix","article_published_time":"2016-01-30T14:20:59+00:00","article_modified_time":"2016-02-01T10:18:17+00:00","author":"fomas","twitter_card":"summary_large_image","twitter_misc":{"Written by":"fomas","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/","url":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/","name":"Shrink MS SQL Temp database using T-SQL - @F0mix","isPartOf":{"@id":"http:\/\/fomas.be\/blog\/#website"},"datePublished":"2016-01-30T14:20:59+00:00","dateModified":"2016-02-01T10:18:17+00:00","author":{"@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b"},"description":"Shrink Temp database Microsoft SQL to prevent expanding disks for keep SQL up and running","breadcrumb":{"@id":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/fomas.be\/blog\/2016\/01\/30\/shrink-tempdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/fomas.be\/blog\/"},{"@type":"ListItem","position":2,"name":"Shrink MS SQL Temp database using T-SQL"}]},{"@type":"WebSite","@id":"http:\/\/fomas.be\/blog\/#website","url":"http:\/\/fomas.be\/blog\/","name":"@F0mix","description":"Passion for SQL and POSH","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/fomas.be\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b","name":"fomas","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g","caption":"fomas"},"url":"http:\/\/fomas.be\/blog\/author\/fomas\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7bTMw-6","jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/6"}],"collection":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/comments?post=6"}],"version-history":[{"count":4,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":21,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/6\/revisions\/21"}],"wp:attachment":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/media?parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/categories?post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/tags?post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}