+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: How do you kill all current connections to a SQL Server 2005 database? (/Thread-How-do-you-kill-all-current-connections-to-a-SQL-Server-2005-database)
How do you kill all current connections to a SQL Server 2005 database? - Sirclutterer36 - 07-31-2023
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.
How can I kill all the connections to the database so that I can rename it?
RE: How do you kill all current connections to a SQL Server 2005 database? - nephritical285421 - 07-31-2023
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'
RE: How do you kill all current connections to a SQL Server 2005 database? - centerfold461402 - 07-31-2023
Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:
USE master
GO
SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
RE: How do you kill all current connections to a SQL Server 2005 database? - quadroon837202 - 07-31-2023
Try this:
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RE: How do you kill all current connections to a SQL Server 2005 database? - sablefishes518629 - 07-31-2023
I've always used:
<pre><code>
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB 'DB_NAME','DB_NAME_NEW'
Go
ALTER DATABASE DB_NAME_NEW SET MULTI_USER -- set back to multi user
GO
</code></pre>
RE: How do you kill all current connections to a SQL Server 2005 database? - slurp5 - 07-31-2023
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.
RE: How do you kill all current connections to a SQL Server 2005 database? - Procolinus466 - 07-31-2023
Kill it, and kill it with fire:
USE master
go
DECLARE @dbname sysname
SET @dbname = 'yourdbname'
DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END
RE: How do you kill all current connections to a SQL Server 2005 database? - cowardly717291 - 07-31-2023
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....
RE: How do you kill all current connections to a SQL Server 2005 database? - padmajr - 07-31-2023
Take offline takes a while and sometimes I experience some problems with that..
Most solid way in my opinion:
**Detach**
Right click DB -> Tasks -> Detach...
check "Drop Connections"
Ok
**Reattach**
Right click Databases -> Attach..
Add... -> select your database, and change the Attach As column to your desired database name.
Ok
RE: How do you kill all current connections to a SQL Server 2005 database? - katrinnurmrlibf - 07-31-2023
Another "kill it with fire" approach is to just restart the MSSQLSERVER service.
I like to do stuff from the commandline. Pasting this exactly into CMD will do it:
NET STOP MSSQLSERVER & NET START MSSQLSERVER
Or open "services.msc" and find "SQL Server (MSSQLSERVER)" and right-click, select "restart".
This will "for sure, for sure" kill ALL connections to ALL databases running on that instance.
(I like this better than many approaches that change and change back the configuration on the server/database)