{"id":682,"date":"2008-07-19T17:45:00","date_gmt":"2008-07-19T17:45:00","guid":{"rendered":"\/blogs\/bobb\/post\/How-do-you-shutdown-a-running-SQLCLR-appdomain.aspx"},"modified":"2013-01-04T01:49:54","modified_gmt":"2013-01-04T09:49:54","slug":"how-do-you-shutdown-a-running-sqlclr-appdomain","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/","title":{"rendered":"How do you shutdown a running SQLCLR appdomain?"},"content":{"rendered":"<p>\nWhen SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the&nbsp;<a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/ddl-appdomains-appear-in-sql-server-log-in-sql-server-2008\/\" class=\"broken_link\">previous blog entry<\/a>), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create\/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down immediately after they are used.&nbsp;A friend of mine recently wanted to shutdown an appdomain on purpose to troubleshoot a problem that he thought might have been SQLCLR-related. So how do you shutdown a runtime appdomain on purpose?\n<\/p>\n<p>\nYou could write a .NET proc to call AppDomain.Unload. But I shied away from this for&nbsp;a few reasons. You&#39;d need to catalog the appdomain as unsafe for the proc to work, which means marking database as trustworthy or doing the &#39;signed assembly with key in master&#39; dance. And I&#39;d really prefer a way to have SQL Server gracefully shutdown the appdomain itself.\n<\/p>\n<p>\nSQL Server will shutdown an appdomain for different reasons. It can shut them down under extremely low memory conditions or when there is a serious enough unhandled exceptional condition (e.g. unhandled exceptional condition that could leave .NET locks in place). We really don&#39;t want to cause either of these on purpose just to shut down&nbsp;an appdomain. Another reason the SQL Server will shut down an appdomain is when a loaded assembly is altered. You can use the ALTER ASSEMBLY DDL statement to replace code in place, subject to limitations. When you alter an assembly in place SQL Server recycles the appdomain to be able to use your new code. Currently executing code will continue to use the appdomain until the call completes; new requests are routed to the new appdomain (with the updated code). When all current requests against the old appdomain complete, the appdomain shuts down. Hmmm, probably not a good idea to muck with recompiling the producting code either. So&#8230;\n<\/p>\n<p>\nCompile a simple do-nothing assembly with a simple do-nothing function (say, add two numbers together). We&#39;ll call the assembly &#39;fred&#39; and the function &#39;addtwo&#39;. The assembly must be owned by the same owner as the appdomain you want to recycle (remember runtime appdomains are on a per database and assembly owner basis). So if the appdomain we want to recycle is the &#39;pubs.dbo[runtime]&#39; appdomain&#8230;\n<\/p>\n<p>\nuse pubs<br \/>\ngo\n<\/p>\n<p>\ncreate assembly fred authorization dbo &#8230;<br \/>\ncreate function dbo.addtwo &#8230;<br \/>\nuse the function dbo.addtwo (this causes the assembly to be loaded)<br \/>\nrecompile the assembly fred<br \/>\nalter assembly fred &#8230; (this cause the eventual appdomain unload)\n<\/p>\n<p>\nNote that you don&#39;t have to change the &#39;fred&#39; assembly, only recompile it. SQL Server decides that an assembly is changed if it has a different MVID (.NET assembly module version identifier). New MVIDs are assigned each time an assembly is recompiled (note that an MVID is not the same as a four-part assembly version number). In Visual Studio, you&#39;d recompile the assembly by using the Recompile menu entry, not the Build menu entry. If you try and run alter assembly without a rebuild you&#39;ll get the error message &#39;ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name &quot;fred&quot;&#39;. You could conceivably use Visual Studio autodeploy for these steps as well, but Visual Studio autodeploy drops the functions and assembly and recreates them rather than using ALTER ASSEMBLY.\n<\/p>\n<p>\nThe next request against any of the .NET database objects will cause a new appdomain to be created.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the&nbsp;previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create\/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-682","post","type-post","status-publish","format-standard","hentry","category-sqlclr"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin<\/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\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the&nbsp;previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create\/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-07-19T17:45:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T09:49:54+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\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\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/\",\"name\":\"How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-07-19T17:45:00+00:00\",\"dateModified\":\"2013-01-04T09:49:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLCLR\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sqlclr\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How do you shutdown a running SQLCLR appdomain?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin","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\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/","og_locale":"en_US","og_type":"article","og_title":"How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin","og_description":"When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the&nbsp;previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create\/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-07-19T17:45:00+00:00","article_modified_time":"2013-01-04T09:49:54+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/","name":"How do you shutdown a running SQLCLR appdomain? - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-07-19T17:45:00+00:00","dateModified":"2013-01-04T09:49:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-do-you-shutdown-a-running-sqlclr-appdomain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQLCLR","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sqlclr\/"},{"@type":"ListItem","position":3,"name":"How do you shutdown a running SQLCLR appdomain?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/682","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=682"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/682\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}