March 10, 2009 at 7:25 am
I was going to change the name of one of my databases so I set it to single user mode and then attempted to change the name and I get database is already open and can only have one user at a time. there is one spid and it is
" exec sp_executeSql N' set implicit_transaction off select USER_NAME() select user_type,type,name from systypes where user type=257"
and if I kill it, it just comes right back.
if I try to set it back to Multi_user I get the database is already open and can only have one user error.
ALTER DATABASE [database] SET MULTI_USER ....
March 10, 2009 at 12:45 pm
Try it WITH ROLLBACK IMMEDIATE
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 10, 2009 at 2:54 pm
I tried that and it gives me the same error.
March 10, 2009 at 3:45 pm
The code should do what you want. Change the DB name from "test" to the database you are working with.
use master
go
declare @kill_spid varchar(20)
-- Find spid of user connection to database test
select @kill_spid= max(spid) from master.dbo.sysprocesses
where dbid in ( select dbid from sysdatabases where name = 'test' )
select [Connection to Kill] = @kill_spid
-- Kill connection to db test
exec ('kill '+@kill_spid )
go
-- set DB test ofline
alter database [test] set offline with rollback immediate
go
-- Bring DB test online in multi user mode
alter database [test] set online, multi_user with rollback immediate
go
use [test]
select Current_DB = db_name()
go
use master
March 10, 2009 at 4:00 pm
Yes that fixed it, I also found good information at http://qa.sqlservercentral.com/Forums/Topic332162-146-1.aspx
Thank You
May 20, 2010 at 3:26 am
SSCommitted
Thank you, your code worked perfectly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply