Cannot make msdb into single user mode

Cannot make msdb into single user mode

Post by Neil Alasta » Sat, 12 Jan 2002 23:32:36



I have checked under SQL Server Agent that it is logging on as me,
pulled through the NT domain. However doing sp_who shows the sa
account logged on. How can I disable this. Ultimately I am trying to
restore the msdb database.

Thanks,

 
 
 

Cannot make msdb into single user mode

Post by chri » Sun, 13 Jan 2002 00:18:26


See KILL in BOL.

Quote:> I have checked under SQL Server Agent that it is logging on as me,
> pulled through the NT domain. However doing sp_who shows the sa
> account logged on. How can I disable this. Ultimately I am trying to
> restore the msdb database.

> Thanks,


 
 
 

Cannot make msdb into single user mode

Post by chri » Sun, 13 Jan 2002 00:26:09


P.S. If you are the SA that you see in sp_who, my last idea wont work. Make
sure that you dont have both Query Analyzer and Enterprise Manager open on
this db. (2 connections)

Quote:> I have checked under SQL Server Agent that it is logging on as me,
> pulled through the NT domain. However doing sp_who shows the sa
> account logged on. How can I disable this. Ultimately I am trying to
> restore the msdb database.

> Thanks,

 
 
 

Cannot make msdb into single user mode

Post by much_to_d » Sun, 13 Jan 2002 01:06:37


Finally got into single user mode with sqlservr.exe -c -m

Went to msdb to restore and it said that only the master could be restored
in single user mode.

Aaarrrrrgggh...

What am I doing wrong? Basically I have all the backups I need on another
servers HD and want to restore to a new sqlserver. I will then change its
name and IP so it will be a hotswop.

Buy the way forgot to mention using sql7 nt4 raid array


> P.S. If you are the SA that you see in sp_who, my last idea wont work.
Make
> sure that you dont have both Query Analyzer and Enterprise Manager open on
> this db. (2 connections)


> > I have checked under SQL Server Agent that it is logging on as me,
> > pulled through the NT domain. However doing sp_who shows the sa
> > account logged on. How can I disable this. Ultimately I am trying to
> > restore the msdb database.

> > Thanks,

 
 
 

Cannot make msdb into single user mode

Post by Jobi » Sun, 13 Jan 2002 01:02:46


see BOL "restore master and msdb" section.

Jobi

> Finally got into single user mode with sqlservr.exe -c -m

> Went to msdb to restore and it said that only the master could be restored
> in single user mode.

> Aaarrrrrgggh...

> What am I doing wrong? Basically I have all the backups I need on another
> servers HD and want to restore to a new sqlserver. I will then change its
> name and IP so it will be a hotswop.

> Buy the way forgot to mention using sql7 nt4 raid array



> > P.S. If you are the SA that you see in sp_who, my last idea wont work.
> Make
> > sure that you dont have both Query Analyzer and Enterprise Manager open
on
> > this db. (2 connections)


> > > I have checked under SQL Server Agent that it is logging on as me,
> > > pulled through the NT domain. However doing sp_who shows the sa
> > > account logged on. How can I disable this. Ultimately I am trying to
> > > restore the msdb database.

> > > Thanks,

 
 
 

Cannot make msdb into single user mode

Post by Sabunim » Mon, 14 Jan 2002 02:31:38


Ensure that you stop the SQL Agent.  that should free the db

> See KILL in BOL.


> > I have checked under SQL Server Agent that it is logging on as me,
> > pulled through the NT domain. However doing sp_who shows the sa
> > account logged on. How can I disable this. Ultimately I am trying to
> > restore the msdb database.

> > Thanks,

 
 
 

1. When Single User Mode is not Single User Mode

Have a process that restores the transaction logs from one SQL Server onto
another every half hour. It first attempts to set the target database into
single user mode and continues with this until successful. Once in single
user mode, it commences the backup using the Restore command.

Sometimes this Restore command fails with the error message "Database in
use. The system administrator must have exclusive use of the database to run
the restore operation".

After some investigation have observed that when a database is in Single
user mode with a connection already present, the 2nd attempt to connect to
it (2nd connection) will timeout after the specified timeout interval. If
additional connections are attempted simulteneousely (any more than 2) these
fail immediately. Whilst the 2nd connection is waiting to timeout, there are
actually 2 connections to the database in single user mode, which causes the
Restore to fail.

Any ideas about why this happens and how it can be avoided/fixed. This is on
SQL Server 7 SP4.

2. Conect Power builder 6.5 to IDS 2000

3. Cannot load any database other than Master when server in single-user mode

4. Command line parameters [Q]

5. Cannot Set SQL 6.5, SP4 master DB to Single User Mode

6. SQL Command are not working

7. Cannot start MSSQL Server in Single User Mode

8. ORACLE SQLFORMS DOCUMENT FOR 2.3 FORMS (form23.rpt)

9. cannot reset "single user mode"

10. Changing server mode (currently in single-user mode) ?

11. single user/multi user mode error messages

12. command to disconnect db users so can switch to single user mode

13. master, msdb won't go to single user