{"id":2440,"date":"2013-02-16T05:44:01","date_gmt":"2013-02-16T05:44:01","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2440"},"modified":"2013-02-16T05:44:01","modified_gmt":"2013-02-16T05:44:01","slug":"drop-connections-with-rollback-immediate-to-allow-a-detach-database","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/02\/16\/drop-connections-with-rollback-immediate-to-allow-a-detach-database\/","title":{"rendered":"Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nIf connections exist on a database , and you attempt to detach a database with EXEC master.dbo.sp_detach_db @dbname = N&#39;database_name&#39; the detach will fail.\r\n\r\nBest practise is to identify the connection sources and communicate with the users, and closing the connection gracefully. Communicating with the users is not always possible .\r\n\r\nInstead of using the KILL command ,use this command to set the database into SINGLE_USER mode. The SINGLE_USER mode indicates for only one user to access the database. The ROLLBACK IMMEDIATE option terminates ,rolling back all transactions not completed and disconnecting all other connections immediately.\r\n\r\n \r\n\r\n \r\n\r\nUSE [master]\r\nGO\r\nALTER DATABASE [database_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nGO\r\nUSE [master]\r\nGO\r\nEXEC master.dbo.sp_detach_db @dbname = N&#39;database_name&#39;\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>If connections exist on a database , and you attempt to [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2440","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2440"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2440\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}