DB in perptual single_user mode
in [Server Configuration]
|
From: Rob on 8 Apr 2010 17:04 Thanks. My default DB is master; I have sysadmin privileges. I checked out this link that describes exactly what I'm experiencing (http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx) but I can't even run the sp_who or any other statements against the sysprocesses table. I'm thinking that I will need to restart SQL Server service to resolve this issue. "RJ Roberts" wrote: > 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: John Bell on 8 Apr 2010 18:44 On Thu, 8 Apr 2010 13:59:01 -0700, Rob <Rob(a)discussions.microsoft.com> wrote: >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 >> . >> What did the select statement return? If that is NULL I would restart SQL Server. John
From: Hugo Kornelis on 9 Apr 2010 05:18 On Thu, 8 Apr 2010 14:04:01 -0700, Rob wrote: >Thanks. My default DB is master; I have sysadmin privileges. I checked out >this link that describes exactly what I'm experiencing >(http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx) but I >can't even run the sp_who or any other statements against the sysprocesses >table. I'm thinking that I will need to restart SQL Server service to resolve >this issue. Hi Rob, Why can't you run sp_who? You don't have to be in the same database for that to run; sp_who will list all activity on the server. -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Rob on 9 Apr 2010 08:28 It didn't return NULL or any other result set; just the message that I had posted. Anyhoo, I restarted SQL Server services and all appears to be fine now. I'll be opening a case with MS for further investigation. Thanks. "John Bell" wrote: > On Thu, 8 Apr 2010 13:59:01 -0700, Rob <Rob(a)discussions.microsoft.com> > wrote: > > >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 > >> . > >> > What did the select statement return? > > If that is NULL I would restart SQL Server. > > John > . >
From: Rob on 9 Apr 2010 08:35 Strange, isn't it? Instead of reporting all activity on the server, sp_who ran partially by reporting on all processes except for activity against DB1 (see my original post on the error message that sp_who reported). A SQL Server services restart did the trick. Next step, open a case with MS to have to investigate this anomaly. Thanks. "Hugo Kornelis" wrote: > On Thu, 8 Apr 2010 14:04:01 -0700, Rob wrote: > > >Thanks. My default DB is master; I have sysadmin privileges. I checked out > >this link that describes exactly what I'm experiencing > >(http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx) but I > >can't even run the sp_who or any other statements against the sysprocesses > >table. I'm thinking that I will need to restart SQL Server service to resolve > >this issue. > > Hi Rob, > > Why can't you run sp_who? You don't have to be in the same database for > that to run; sp_who will list all activity on the server. > > -- > Hugo Kornelis, SQL Server MVP > My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Is store procedure always fast than Access linked table via ODBC? Next: SQL 2008 on SBS 2003 |