Menu
I need to rename the database, but I cannot since other processes is using it. I don't care about the process, and I need to kill it.
How do I remove all connections from the db?
301
Right-click the server in Object Explorer and select 'Activity Monitor'. When this opens, expand the Processes group. Now use the drop-down to filter the results by database name. Kill off the server connections by selecting the right-click 'Kill Process' option.
Right-click on a database in SSMS and choose delete. In the dialog, check the checkbox for "Close existing connections." Click the Script button at the top of the dialog.
Step 1. : Connect to SQL Server instance and open SSMS. Step 2: Go to Tools tab in SSMS and select Option. Step 3 : Select Query Result option and then go to SQL Server and Result to Grid. Step 4 : Enable check box Retain CR/LF on copy or save and click OK.
As per my comment once in single_user mode you need to do the rename from the same connection. Don't try and rename it through Object Explorer as that will try and open up a new connection. The following works this end...
ALTER DATABASE AdventureWorks SET single_user WITH ROLLBACK IMMEDIATE
ALTER DATABASE AdventureWorks MODIFY NAME = NewAdventureWorks
ALTER DATABASE NewAdventureWorks SET multi_user
Like this: Kill All Active Connections To A Database
ALTER DATABASE oldNameSET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_renamedb 'oldName', 'newName'
ALTER DATABASE newName SET MULTI_USER --new name of course
Run sp_who, then kill [pid] for everyone in your database.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
