June 1, 2002 at 4:54 pm
(SQL Server 2000 Standard)
How does one put the default instance in single user mode?
error message in Enterprise Manager:
"A connection could not be established to SQL111. Reason: Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time."
HELP ????
June 1, 2002 at 5:39 pm
Usually it is done thru command line switch at startup. Check you service to see if any switches were added. Also usually the first person in does not have this issue so someone else has to already be in, disable the network, stop the service and restart the service, then try to get in and see if has the same problem. From SQL BOL
quote:
Starting SQL Server in Single-User ModeUnder certain circumstances, you may need to start an instance of Microsoft® SQL Server™ in single-user mode using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.
When you start an instance of SQL Server in single-user mode:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.
The sp_configure system stored procedure allow updates option is enabled. By default, the allow updates option is disabled.
To start SQL Server in single-user mode
Command Prompt
How to start the default instance of SQL Server in single-user mode (Command Prompt)
To start the default instance of SQL Server in single-user mode from a command prompt
From a command prompt, enter:
sqlservr.exe -c -m
Note You must switch to the appropriate directory (for the instance of Microsoft® SQL Server™ you want to start) in the command window before starting sqlservr.exe.
See Also
Starting SQL Server in Single-User Mode
Using Startup Options
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 1, 2002 at 5:45 pm
the switches I entered are good.
No one is in the database.
???
June 2, 2002 at 8:15 am
You didn't happen to try connecting with two apps did you? Ex. QA was connected before you were opening EM. Also what is the problem you are having, is it trying to connect or the fact it is in Single User Mode? Your initial question sounds more like the later.
Almost forgot, you did not restart Agent did you? Agent will connect to the server for it's database.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/02/2002 08:16:12 AM
June 3, 2002 at 8:59 am
It connects in Single User mode, but when I go to EM I cannot get in.
June 3, 2002 at 9:39 am
I do the same thing and get in fine with EM unless I open a QA or other session to the box. You must have something talking to the box already that is locking you out before you get into EM, is this server used as a linked server from another location? There has got to be something specific in your way. Stop and start normal then run sp_who to get an idea of who or what possibly. If you are sure no other possibility then I have no other ideas except stop the other instances when you do this and see if it was one of them.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 12:09 pm
I do the command propmt switch to go to single user mode. At this point who is in the database server? (My Domain Account???)
June 3, 2002 at 6:00 pm
Not sure. This is the steps I follow.
Shutdown SQL
Open Command Prompt
Type path and sqlservr.exe -c -m (Copied from service command line)
Wait recovery complete message
Open EM and drill to server
I have no issues, however if I try to open say QA I get the exact message so something is getting in and blocking you somehow that is why I suggested possible linked server connection, outside app/web, or another user.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 11:00 pm
Hi
Ive had problems with using the command line and then trying to use EM against the instance (comes up with an error re too many administrators logged in etc). To get around this:
a) goto services
b) open up the instance service dialog
c) enter command line options (DONT CLOSE THE DIALOG)
d) start the instance
then go to EM and all works fine in single user mode.
When ready, shutdown service via same dialog. The command line options do not persist with subsequent re-starts of the instance, thats why in c) i said "dont close the dialog". Of course this is what ive found on my servers but you never know with Windows and your particular installation.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply