DB USER Status - 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)

DB USER Status

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-26 : 22:47:23

Can anyone tell me how to change the database from Single user to Multi user, as Database now in Single user mode and unable to open its properties to get it changed to Multi-user mode.

Thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-26 : 22:49:42

Did you search from your side?

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-26 : 22:53:59

I tried to change from Single user to Multi User on opening the property sheet of the DB, but it says database is already in use and cannot to opened.

In Fact it didnt allowed me to access the database.

Any clue please..

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-01-27 : 04:34:12

Open your Activity Monitor and look for any open connections to this database. Close these connections and then you will be able to open the properties of the database and change it to multi-user mode.

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 06:29:05

Or simply run the query
DECLARE @spid INT
DECLARE @tString VARCHAR(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
SELECT spid
FROM sysprocesses
where db_name(dbid) = 'test2008'
OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
GO
alter database test2008 set multi_user

change test2008 to your database name

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-28 : 17:00:48

Thanks all for your contribution.

However, I have killed the ONLY USER from different session as said earlier, then able to open the property sheet to get it changed.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 17:34:48

Welcome.

Glad you did it.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-28 : 21:44:31

Thanks So Deep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources