Prev: Is store procedure always fast than Access linked table via ODBC?
Next: SQL 2008 on SBS 2003
From: Rob on 8 Apr 2010 14:34
Hello:

We have a DB that currently appears to be in a single user mode only, and
attempts to alter this DB to multi users isn't working:

alter database DB1
set multi_user --with rollback immediate
/*
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'DB1' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
*/

Attempt to manually run the restore command also fails (we use SQL Litespeed):

/*
Msg 62301, Level 16, State 1, Line 0
SQL Server has returned a failure message to LiteSpeed for SQL Server which
has prevented the operation from succeeding.
The following message is not a LiteSpeed for SQL Server message. Please
refer to SQL Server books online or Microsoft technical support for a
solution:

RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use.
*/

And so do the following commands (Kill, Drop, sp_who):

DECLARE @cmdKill VARCHAR(50)

DECLARE killCursor CURSOR FOR
SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
FROM master.dbo.sysprocesses AS p
WHERE p.dbid = db_id('DB1')

OPEN killCursor
FETCH killCursor INTO @cmdKill

WHILE 0 = @@fetch_status
BEGIN
EXECUTE (@cmdKill)
FETCH killCursor INTO @cmdKill
END

CLOSE killCursor
DEALLOCATE killCursor

/*
Msg 924, Level 14, State 1, Line 8
Database 'DB1' is already open and can only have one user at a time.
*/

drop database DB1

/*
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "DB1" because it is currently in use.
*/

sp_who --Reports processes for all other SPIDs except for DB1

/*
Msg 924, Level 14, State 1, Procedure sp_who, Line 79
Database 'DB1' is already open and can only have one user at a time.
*/

sp_who2

/*
Msg 924, Level 14, State 1, Procedure sp_who, Line 79
Database 'DB1' is already open and can only have one user at a time.
*/

I have also attempted to restart the Agent service, detach the DB, take it
offline, but none of those worked. This DB is hosted on a consolidated
platform so a SQL service restart would be my last option if all else fails.

Any help, suggestions, thoughts, or ideas are welcome.

Thanks.
From: John Bell on 8 Apr 2010 15:34
On Thu, 8 Apr 2010 11:34:01 -0700, Rob <Rob(a)discussions.microsoft.com>
wrote:

>Hello:
>
>We have a DB that currently appears to be in a single user mode only, and
>attempts to alter this DB to multi users isn�t working:
>
>alter database DB1
>set multi_user --with rollback immediate
>/*
>Msg 5064, Level 16, State 1, Line 1
>Changes to the state or options of database 'DB1' cannot be made at this
>time. The database is in single-user mode, and a user is currently connected
>to it.
>Msg 5069, Level 16, State 1, Line 1
>ALTER DATABASE statement failed.
>*/
>
>Attempt to manually run the restore command also fails (we use SQL Litespeed):
>
>/*
>Msg 62301, Level 16, State 1, Line 0
>SQL Server has returned a failure message to LiteSpeed for SQL Server which
>has prevented the operation from succeeding.
>The following message is not a LiteSpeed for SQL Server message. Please
>refer to SQL Server books online or Microsoft technical support for a
>solution:
>
>RESTORE DATABASE is terminating abnormally.
>Exclusive access could not be obtained because the database is in use.
>*/
>
>And so do the following commands (Kill, Drop, sp_who):
>
>DECLARE @cmdKill VARCHAR(50)
>
>DECLARE killCursor CURSOR FOR
>SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
>FROM master.dbo.sysprocesses AS p
>WHERE p.dbid = db_id('DB1')
>
>OPEN killCursor
>FETCH killCursor INTO @cmdKill
>
>WHILE 0 = @@fetch_status
>BEGIN
>EXECUTE (@cmdKill)
>FETCH killCursor INTO @cmdKill
>END
>
>CLOSE killCursor
>DEALLOCATE killCursor
>
>/*
>Msg 924, Level 14, State 1, Line 8
>Database 'DB1' is already open and can only have one user at a time.
>*/
>
>drop database DB1
>
>/*
>Msg 3702, Level 16, State 4, Line 1
>Cannot drop database "DB1" because it is currently in use.
>*/
>
>sp_who --Reports processes for all other SPIDs except for DB1
>
>/*
>Msg 924, Level 14, State 1, Procedure sp_who, Line 79
>Database 'DB1' is already open and can only have one user at a time.
>*/
>
>sp_who2
>
>/*
>Msg 924, Level 14, State 1, Procedure sp_who, Line 79
>Database 'DB1' is already open and can only have one user at a time.
>*/
>
>I have also attempted to restart the Agent service, detach the DB, take it
>offline, but none of those worked. This DB is hosted on a consolidated
>platform so a SQL service restart would be my last option if all else fails.
>
>Any help, suggestions, thoughts, or ideas are welcome.
>
>Thanks.

Try something like:

DECLARE @cmd varchar(20) ;
SET @cmd = ( SELECT 'KILL ' + CAST(spid as varchar(15))
FROM sys.sysprocesses WHERE dbid = (
SELECT database_id
FROM sys.databases
WHERE name = 'DB1' ) )
;

SELECT @cmd ;
EXEC ( @cmd ) ;

John
From: RJ Roberts on 8 Apr 2010 16:04

You may be getting burned having DB1 the default database for the user login
you are using. Often times it is your own login that prevents the setting to
multi mode.

Execute sp_who2 to see who is attached to DB1 and kill the sessions.
Change the default login for yourself to Master and then execute ALTER
DATABASE db1 SET MULTI_USER

HTH--
RJ Roberts
DB Architect/Developer


"Rob" wrote:

> Hello:
>
> We have a DB that currently appears to be in a single user mode only, and
> attempts to alter this DB to multi users isn't working:
>
> alter database DB1
> set multi_user --with rollback immediate
> /*
> Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'DB1' cannot be made at this
> time. The database is in single-user mode, and a user is currently connected
> to it.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> */
>
> Attempt to manually run the restore command also fails (we use SQL Litespeed):
>
> /*
> Msg 62301, Level 16, State 1, Line 0
> SQL Server has returned a failure message to LiteSpeed for SQL Server which
> has prevented the operation from succeeding.
> The following message is not a LiteSpeed for SQL Server message. Please
> refer to SQL Server books online or Microsoft technical support for a
> solution:
>
> RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use.
> */
>
> And so do the following commands (Kill, Drop, sp_who):
>
> DECLARE @cmdKill VARCHAR(50)
>
> DECLARE killCursor CURSOR FOR
> SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
> FROM master.dbo.sysprocesses AS p
> WHERE p.dbid = db_id('DB1')
>
> OPEN killCursor
> FETCH killCursor INTO @cmdKill
>
> WHILE 0 = @@fetch_status
> BEGIN
> EXECUTE (@cmdKill)
> FETCH killCursor INTO @cmdKill
> END
>
> CLOSE killCursor
> DEALLOCATE killCursor
>
> /*
> Msg 924, Level 14, State 1, Line 8
> Database 'DB1' is already open and can only have one user at a time.
> */
>
> drop database DB1
>
> /*
> Msg 3702, Level 16, State 4, Line 1
> Cannot drop database "DB1" because it is currently in use.
> */
>
> sp_who --Reports processes for all other SPIDs except for DB1
>
> /*
> Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> Database 'DB1' is already open and can only have one user at a time.
> */
>
> sp_who2
>
> /*
> Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> Database 'DB1' is already open and can only have one user at a time.
> */
>
> I have also attempted to restart the Agent service, detach the DB, take it
> offline, but none of those worked. This DB is hosted on a consolidated
> platform so a SQL service restart would be my last option if all else fails.
>
> Any help, suggestions, thoughts, or ideas are welcome.
>
> Thanks.
From: RJ Roberts on 8 Apr 2010 16:21
If DB1 is your default database might need to change the default using SQLCMD
as opposed to SSMS. If so...

Put the following in a bat file
SqlCmd -S YourServerName -d master -E
Execute the bat file
At the Command Prompt type
>sp_defaultdb 'yourusername', master
>Go
After execution
>Exit
Now try using SSMS and executing
ALTER DATABASE DB1 SET MULTI_USER


--
RJ Roberts
DB Architect/Developer


"RJ Roberts" wrote:

>
> You may be getting burned having DB1 the default database for the user login
> you are using. Often times it is your own login that prevents the setting to
> multi mode.
>
> Execute sp_who2 to see who is attached to DB1 and kill the sessions.
> Change the default login for yourself to Master and then execute ALTER
> DATABASE db1 SET MULTI_USER
>
> HTH--
> RJ Roberts
> DB Architect/Developer
>
>
> "Rob" wrote:
>
> > Hello:
> >
> > We have a DB that currently appears to be in a single user mode only, and
> > attempts to alter this DB to multi users isn't working:
> >
> > alter database DB1
> > set multi_user --with rollback immediate
> > /*
> > Msg 5064, Level 16, State 1, Line 1
> > Changes to the state or options of database 'DB1' cannot be made at this
> > time. The database is in single-user mode, and a user is currently connected
> > to it.
> > Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> > */
> >
> > Attempt to manually run the restore command also fails (we use SQL Litespeed):
> >
> > /*
> > Msg 62301, Level 16, State 1, Line 0
> > SQL Server has returned a failure message to LiteSpeed for SQL Server which
> > has prevented the operation from succeeding.
> > The following message is not a LiteSpeed for SQL Server message. Please
> > refer to SQL Server books online or Microsoft technical support for a
> > solution:
> >
> > RESTORE DATABASE is terminating abnormally.
> > Exclusive access could not be obtained because the database is in use.
> > */
> >
> > And so do the following commands (Kill, Drop, sp_who):
> >
> > DECLARE @cmdKill VARCHAR(50)
> >
> > DECLARE killCursor CURSOR FOR
> > SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
> > FROM master.dbo.sysprocesses AS p
> > WHERE p.dbid = db_id('DB1')
> >
> > OPEN killCursor
> > FETCH killCursor INTO @cmdKill
> >
> > WHILE 0 = @@fetch_status
> > BEGIN
> > EXECUTE (@cmdKill)
> > FETCH killCursor INTO @cmdKill
> > END
> >
> > CLOSE killCursor
> > DEALLOCATE killCursor
> >
> > /*
> > Msg 924, Level 14, State 1, Line 8
> > Database 'DB1' is already open and can only have one user at a time.
> > */
> >
> > drop database DB1
> >
> > /*
> > Msg 3702, Level 16, State 4, Line 1
> > Cannot drop database "DB1" because it is currently in use.
> > */
> >
> > sp_who --Reports processes for all other SPIDs except for DB1
> >
> > /*
> > Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> > Database 'DB1' is already open and can only have one user at a time.
> > */
> >
> > sp_who2
> >
> > /*
> > Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> > Database 'DB1' is already open and can only have one user at a time.
> > */
> >
> > I have also attempted to restart the Agent service, detach the DB, take it
> > offline, but none of those worked. This DB is hosted on a consolidated
> > platform so a SQL service restart would be my last option if all else fails.
> >
> > Any help, suggestions, thoughts, or ideas are welcome.
> >
> > Thanks.
From: Rob on 8 Apr 2010 16:59
Thanks. Here's the result, which is similar to what I had seen with my own
KILL script:

Msg 924, Level 14, State 1, Line 2
Database 'DB1' is already open and can only have one user at a time.

"John Bell" wrote:

> On Thu, 8 Apr 2010 11:34:01 -0700, Rob <Rob(a)discussions.microsoft.com>
> wrote:
>
> >Hello:
> >
> >We have a DB that currently appears to be in a single user mode only, and
> >attempts to alter this DB to multi users isn't working:
> >
> >alter database DB1
> >set multi_user --with rollback immediate
> >/*
> >Msg 5064, Level 16, State 1, Line 1
> >Changes to the state or options of database 'DB1' cannot be made at this
> >time. The database is in single-user mode, and a user is currently connected
> >to it.
> >Msg 5069, Level 16, State 1, Line 1
> >ALTER DATABASE statement failed.
> >*/
> >
> >Attempt to manually run the restore command also fails (we use SQL Litespeed):
> >
> >/*
> >Msg 62301, Level 16, State 1, Line 0
> >SQL Server has returned a failure message to LiteSpeed for SQL Server which
> >has prevented the operation from succeeding.
> >The following message is not a LiteSpeed for SQL Server message. Please
> >refer to SQL Server books online or Microsoft technical support for a
> >solution:
> >
> >RESTORE DATABASE is terminating abnormally.
> >Exclusive access could not be obtained because the database is in use.
> >*/
> >
> >And so do the following commands (Kill, Drop, sp_who):
> >
> >DECLARE @cmdKill VARCHAR(50)
> >
> >DECLARE killCursor CURSOR FOR
> >SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
> >FROM master.dbo.sysprocesses AS p
> >WHERE p.dbid = db_id('DB1')
> >
> >OPEN killCursor
> >FETCH killCursor INTO @cmdKill
> >
> >WHILE 0 = @@fetch_status
> >BEGIN
> >EXECUTE (@cmdKill)
> >FETCH killCursor INTO @cmdKill
> >END
> >
> >CLOSE killCursor
> >DEALLOCATE killCursor
> >
> >/*
> >Msg 924, Level 14, State 1, Line 8
> >Database 'DB1' is already open and can only have one user at a time.
> >*/
> >
> >drop database DB1
> >
> >/*
> >Msg 3702, Level 16, State 4, Line 1
> >Cannot drop database "DB1" because it is currently in use.
> >*/
> >
> >sp_who --Reports processes for all other SPIDs except for DB1
> >
> >/*
> >Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> >Database 'DB1' is already open and can only have one user at a time.
> >*/
> >
> >sp_who2
> >
> >/*
> >Msg 924, Level 14, State 1, Procedure sp_who, Line 79
> >Database 'DB1' is already open and can only have one user at a time.
> >*/
> >
> >I have also attempted to restart the Agent service, detach the DB, take it
> >offline, but none of those worked. This DB is hosted on a consolidated
> >platform so a SQL service restart would be my last option if all else fails.
> >
> >Any help, suggestions, thoughts, or ideas are welcome.
> >
> >Thanks.
>
> Try something like:
>
> DECLARE @cmd varchar(20) ;
> SET @cmd = ( SELECT 'KILL ' + CAST(spid as varchar(15))
> FROM sys.sysprocesses WHERE dbid = (
> SELECT database_id
> FROM sys.databases
> WHERE name = 'DB1' ) )
> ;
>
> SELECT @cmd ;
> EXEC ( @cmd ) ;
>
> John
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Is store procedure always fast than Access linked table via ODBC?
Next: SQL 2008 on SBS 2003