<p>I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active.</p>

<p>How can I kill all the connections to the database so that I can rename it?</p>

[{'answer_id': 11623, 'author': 'John Christensen', 'author_id': 1194, 'author_profile': 'https://Stackoverflow.com/users/1194', 'pm_score': 2, 'selected': False, 'text': "<p>In MS SQL Server Management Studio on the object explorer, right click on the database. In the context menu that follows select 'Tasks -> Take Offline'</p>\n"}, {'answer_id': 11624, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 10, 'selected': True, 'text': '<p>The reason that the approach that <a href="https://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database/11627#11627">Adam suggested</a> won\'t work is that during the time that you are looping over the active connections new one can be established, and you\'ll miss those. You could instead use the following approach which does not have this drawback:</p>\n\n<pre><code>-- set your current connection to use master otherwise you might get an error\n\nuse master\nALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE \n\n--do you stuff here \n\nALTER DATABASE YourDatabase SET MULTI_USER\n</code></pre>\n'}, {'answer_id': 11627, 'author': 'Adam', 'author_id': 1341, 'author_profile': 'https://Stackoverflow.com/users/1341', 'pm_score': 7, 'selected': False, 'text': "<p>Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:</p>\n\n<pre><code>USE master\nGO\n\nSET NOCOUNT ON\nDECLARE @DBName varchar(50)\nDECLARE @spidstr varchar(8000)\nDECLARE @ConnKilled smallint\nSET @ConnKilled=0\nSET @spidstr = ''\n\nSet @DBName = 'DB_NAME'\nIF db_id(@DBName) &lt; 4\nBEGIN\nPRINT 'Connections to system databases cannot be killed'\nRETURN\nEND\nSELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '\nFROM master..sysprocesses WHERE dbid=db_id(@DBName)\n\nIF LEN(@spidstr) &gt; 0\nBEGIN\nEXEC(@spidstr)\nSELECT @ConnKilled = COUNT(1)\nFROM master..sysprocesses WHERE dbid=db_id(@DBName)\nEND\n</code></pre>\n"}, {'answer_id': 11629, 'author': 'Joseph Daigle', 'author_id': 507, 'author_profile': 'https://Stackoverflow.com/users/507', 'pm_score': 5, 'selected': False, 'text': '<p>Using SQL Management Studio Express:</p>\n\n<p>In the Object Explorer tree drill down under Management to "Activity Monitor" (if you cannot find it there then right click on the database server and select "Activity Monitor"). Opening the Activity Monitor, you can view all process info. You should be able to find the locks for the database you\'re interested in and kill those locks, which will also kill the connection.</p>\n\n<p>You should be able to rename after that. </p>\n'}, {'answer_id': 11630, 'author': 'Joseph Sturtevant', 'author_id': 317, 'author_profile': 'https://Stackoverflow.com/users/317', 'pm_score': 2, 'selected': False, 'text': '<p>Try this:</p>\n\n<pre><code>ALTER DATABASE [DATABASE_NAME]\nSET SINGLE_USER\nWITH ROLLBACK IMMEDIATE\n</code></pre>\n'}, {'answer_id': 11633, 'author': 'brendan', 'author_id': 225, 'author_profile': 'https://Stackoverflow.com/users/225', 'pm_score': 5, 'selected': False, 'text': "<p>I've always used:</p>\n\n<pre><code>\nALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE \nGO \nSP_RENAMEDB 'DB_NAME','DB_NAME_NEW'\nGo \nALTER DATABASE DB_NAME_NEW  SET MULTI_USER -- set back to multi user \nGO \n</code></pre>\n"}, {'answer_id': 12413, 'author': 'RedWolves', 'author_id': 648, 'author_profile': 'https://Stackoverflow.com/users/648', 'pm_score': 3, 'selected': False, 'text': "<p>I usually run into that error when I am trying to restore a database I usually just go to the top of the tree in Management Studio and right click and restart the database server (because it's on a development machine, this might not be ideal in production).  This is close all database connections.</p>\n"}, {'answer_id': 2817992, 'author': 'btk', 'author_id': 289255, 'author_profile': 'https://Stackoverflow.com/users/289255', 'pm_score': 6, 'selected': False, 'text': "<p>Kill it, and kill it with fire:</p>\n\n<pre><code>USE master\ngo\n\nDECLARE @dbname sysname\nSET @dbname = 'yourdbname'\n\nDECLARE @spid int\nSELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)\nWHILE @spid IS NOT NULL\nBEGIN\nEXECUTE ('KILL ' + @spid)\nSELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid &gt; @spid\nEND\n</code></pre>\n"}, {'answer_id': 4161622, 'author': 'alirobe', 'author_id': 114149, 'author_profile': 'https://Stackoverflow.com/users/114149', 'pm_score': 2, 'selected': False, 'text': "<p>Here's how to reliably this sort of thing in MS SQL Server Management Studio 2008 (may work for other versions too):</p>\n\n<ol>\n<li>In the Object Explorer Tree, right click the root database server (with the green arrow), then click activity monitor.</li>\n<li>Open the processes tab in the activity monitor, select the 'databases' drop down menu, and filter by the database you want.</li>\n<li>Right click the DB in Object Explorer and start a 'Tasks -> Take Offline' task. Leave this running in the background while you...</li>\n<li>Safely shut down whatever you can.</li>\n<li>Kill all remaining processes from the process tab.</li>\n<li>Bring the DB back online.</li>\n<li>Rename the DB.</li>\n<li>Bring your service back online and point it to the new DB.</li>\n</ol>\n"}, {'answer_id': 4183746, 'author': 'Sanjay Saxena', 'author_id': 508165, 'author_profile': 'https://Stackoverflow.com/users/508165', 'pm_score': 2, 'selected': False, 'text': '<p>Right click on the database name, click on Property to get property window, Open the Options tab and change the "Restrict Access" property from Multi User to Single User. When you hit on OK button, it will prompt you to closes all open connection, select "Yes" and you are set to rename the database....</p>\n'}, {'answer_id': 4605373, 'author': 'NJV', 'author_id': 564076, 'author_profile': 'https://Stackoverflow.com/users/564076', 'pm_score': 4, 'selected': False, 'text': '<p>Take offline takes a while and sometimes I experience some problems with that..</p>\n\n<p>Most solid way in my opinion:</p>\n\n<p><strong>Detach</strong>\nRight click DB -> Tasks -> Detach...\ncheck "Drop Connections" \nOk</p>\n\n<p><strong>Reattach</strong>\nRight click Databases -> Attach..\nAdd... -> select your database, and change the Attach As column to your desired database name.\nOk</p>\n'}, {'answer_id': 7114816, 'author': 'aikeru', 'author_id': 76840, 'author_profile': 'https://Stackoverflow.com/users/76840', 'pm_score': 2, 'selected': False, 'text': '<p>Another "kill it with fire" approach is to just restart the MSSQLSERVER service.\nI like to do stuff from the commandline. Pasting this exactly into CMD will do it:\nNET STOP MSSQLSERVER &amp; NET START MSSQLSERVER</p>\n\n<p>Or open "services.msc" and find "SQL Server (MSSQLSERVER)" and right-click, select "restart".</p>\n\n<p>This will "for sure, for sure" kill ALL connections to ALL databases running on that instance.</p>\n\n<p>(I like this better than many approaches that change and change back the configuration on the server/database)</p>\n'}, {'answer_id': 8653964, 'author': 'Lars Timenes', 'author_id': 1118999, 'author_profile': 'https://Stackoverflow.com/users/1118999', 'pm_score': 2, 'selected': False, 'text': '<p>The option working for me in this scenario is as follows: </p>\n\n<ol>\n<li>Start the "Detach" operation on the database in question. This wil open a window (in SQL 2005) displaying the active connections that prevents actions on the DB. </li>\n<li>Kill the active connections, cancel the detach-operation. </li>\n<li>The database should now be available for restoring.</li>\n</ol>\n'}, {'answer_id': 9093487, 'author': 'Talha', 'author_id': 1023687, 'author_profile': 'https://Stackoverflow.com/users/1023687', 'pm_score': 3, 'selected': False, 'text': "<pre><code>Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp\nfrom master.dbo.sysprocesses p (nolock)\njoin master..sysdatabases d (nolock) on p.dbid = d.dbid\nWhere d.[name] = 'your db name'\n\nDeclare @query nvarchar(max)\n--Select * from #temp\nSelect @query =STUFF((                              \n            select '  ' + KillCommand from #temp\n            FOR XML PATH('')),1,1,'') \nExecute sp_executesql @query \nDrop table #temp\n</code></pre>\n\n<p>use the 'master' database and run this query, it will kill all the active connections from your database.</p>\n"}, {'answer_id': 10393091, 'author': 'The Coder', 'author_id': 1129108, 'author_profile': 'https://Stackoverflow.com/users/1129108', 'pm_score': 2, 'selected': False, 'text': "<p>These didn't work for me (SQL2008 Enterprise), I also couldn't see any running processes or users connected to the DB.  Restarting the server (Right click on Sql Server in Management Studio and pick Restart) allowed me to restore the DB.</p>\n"}, {'answer_id': 10995373, 'author': 'Ilmar', 'author_id': 1451048, 'author_profile': 'https://Stackoverflow.com/users/1451048', 'pm_score': 2, 'selected': False, 'text': '<p>I\'m using SQL Server 2008 R2, my DB was already set for single user and there was a connection that restricted any action on the database. Thus the recommended <a href="https://stackoverflow.com/a/11624/1451048">SQLMenace\'s</a> solution responded with error. <a href="http://blog.tech-cats.com/2008/01/kill-all-database-connections-to-sql.html" rel="nofollow noreferrer">Here is one that worked in my case</a>.</p>\n'}, {'answer_id': 11536813, 'author': 'santhosh kumar', 'author_id': 1534044, 'author_profile': 'https://Stackoverflow.com/users/1534044', 'pm_score': 4, 'selected': False, 'text': '<pre><code>ALTER DATABASE [Test]\nSET OFFLINE WITH ROLLBACK IMMEDIATE\n\nALTER DATABASE [Test]\nSET ONLINE\n</code></pre>\n'}, {'answer_id': 23991847, 'author': 'mehdi lotfi', 'author_id': 1407421, 'author_profile': 'https://Stackoverflow.com/users/1407421', 'pm_score': -1, 'selected': False, 'text': '<p>You can Use SP_Who command and kill all process that use your database and then rename your database.</p>\n'}, {'answer_id': 30097342, 'author': 'Ray Krungkaew', 'author_id': 1554116, 'author_profile': 'https://Stackoverflow.com/users/1554116', 'pm_score': 0, 'selected': False, 'text': "<p>I use sp_who to get list of all process in database. This is better because you may want to review which process to kill.</p>\n\n<pre><code>declare @proc table(\n    SPID bigint,\n    Status nvarchar(255),\n    Login nvarchar(255),\n    HostName nvarchar(255),\n    BlkBy nvarchar(255),\n    DBName nvarchar(255),\n    Command nvarchar(MAX),\n    CPUTime bigint,\n    DiskIO bigint,\n    LastBatch nvarchar(255),\n    ProgramName nvarchar(255),\n    SPID2 bigint,\n    REQUESTID bigint\n)\n\ninsert into @proc\nexec sp_who2\n\nselect  *, KillCommand = concat('kill ', SPID, ';')\nfrom    @proc\n</code></pre>\n\n<p><strong>Result</strong><br>\nYou can use command in KillCommand column to kill the process you want to.</p>\n\n<pre><code>SPID    KillCommand\n26      kill 26;\n27      kill 27;\n28      kill 28;\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/11620', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1341/']