How to locate and kill all active connections?
-
I am trying to migrate (copy) a database from Server A to Server B using the CDW Feature. I am receiving "Failed DBName Has 1 Active Connection". I have done the Sp_Who and Kill Commands but I am still getting this message. All users are off the database. Is there something I am missing? Should I try stopping the SQL Agent Service? What other method may I use to check for active connections? (We have SQL Server 2000, SP3.)
Thanks in advance, Kevin
-
Use this script....it will kill all connections and take the database off line before anyone can re-attach to it.
Have Fun
Eric
Declare @tblConnectedUsers Table ( SPID int  
Declare @vcSQLText varchar(200), @iSPID int
--Get the currently connected users
Insert into @tblConnectedUsers
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'BJCDEMOESS' --> database name here
--Loop though the connected users and kill their connections
While 1 = 1
Begin
Select top 1 @iSPID = SPID
From @tblConnectedUsers
Where SPID > IsNull(@iSPID, 0)
order by SPID asc
-- break when there are no more SPIDs
If @@RowCount = 0
Break
--Build the SQL string
Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID)
Exec( @vcSQLText )
End
alter database BJCDEMOESS set offline
-
Use this script....it will kill all connections and take the database off line before anyone can re-attach to it.
Have Fun
Eric
Declare @tblConnectedUsers Table ( SPID int)
Declare @vcSQLText varchar(200), @iSPID int
--Get the currently connected users
Insert into @tblConnectedUsers
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'BJCDEMOESS' --> database name here
--Loop though the connected users and kill their connections
While 1 = 1
Begin
Select top 1 @iSPID = SPID
From @tblConnectedUsers
Where SPID > IsNull(@iSPID, 0)
order by SPID asc
-- break when there are no more SPIDs
If @@RowCount = 0
Break
--Build the SQL string
Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID)
Exec( @vcSQLText )
End
alter database BJCDEMOESS set offline
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply