single user mode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

single user mode

Author

Topic

sqlserverdba
Yak Posting Veteran

53 Posts

Posted - 2009-07-24 : 16:15:16

Hi,

When I changed database into single user mode. I want to revery back. it's saying someone is accessing. how to fix this issue?
Thanks

sqlserverdba
Yak Posting Veteran

53 Posts

Posted - 2009-07-24 : 16:39:41

please help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-24 : 17:56:16

[code]
declare @db_name sysname
declare @spid_to_kill int
declare @sql nvarchar(4000)

-- set database name
set @db_name = 'pubs'

-- Find spid of user process in database
select top 1
@spid_to_kill = spid
from
sysprocesses a
join
sysdatabases b
on a.dbid = b.dbid
where
b.name = @db_name

set @sql =
'
use master
-- Kill user process connected to database
kill '+convert(nvarchar(10),@spid_to_kill)+'
-- Set database to multi user
alter database '+quotename(@db_name)+' set multi_user with rollback immediate
'

print isnull(@sql,'NULL')

if @sql is not null begin exec (@sql) end
[/code]

Results:
[code]
use master
-- Kill user process connected to database
kill 55
-- Set database to multi user
alter database [pubs] set multi_user with rollback immediate

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources