September 3, 2007 at 7:51 am
Stopped the sqlserver agent first.
included -m option in the startup options of the SQL SERVER virtual server .
restarted the sql server ..
Now it is in single user mode.
after this I cannot do anything I cannot even right click on the server
" throws me error saying server in single user mode user cannot login"
Database is sqlserver 2000
os win2003
clustered 2 nodes.
rebooted the nodes , restarted the sql server many times tried login with sa, services running user account.
still cannot login into the server thru EM from outside and from server itself.
sql server agent cannot be brought up...
how do I overcome this now.........Need to bring the server in multiple user mode...
any suggestions will be greatly appriciated.
Thanks
September 3, 2007 at 8:23 am
Try to use osql instead of EM.
Do disable the SQL Agent (it also can take the connection away)
If this fails, make sure that the Cluster Administrator does not take the only connection.
Andras
September 3, 2007 at 8:30 am
This is 2000 so there is no management studio....
I restarted the sql server many times even the nodes too but still it doesnt allow me to login becoz the startup parameters are set with -m option......
all I need is to remove the -m option from the startup parameters.
I appreciate your help ....but can you elaborate on the answer you posted.....
"The object explorer, registered server windows in Management Studio all use a connection. Close these, just in case restart Management Studio, but do not log in when it asks. Then Query->Connection->Connect.
Or even better, use osql instead of Management Studio."
object explorer....I haven't opened any object explorer...
registered server windows...? I cannot even right click on the EM registered server it says cannot login.....
it is taking connection from somewhere
September 3, 2007 at 8:45 am
Sorry, before your comment I have updated my post to refer to 2000. The Management Studio stuff is not relevant.
can you connect using osql?
Andras
September 3, 2007 at 8:56 am
Could you also try to start your server from the command prompt and then connect via osql?
Andras
September 3, 2007 at 12:39 pm
did try thru OSQL......but still cannot connect....saya can only connect one administartor when in single user mode...
how to make sure that cluster administrator is not taking up the connection.....I did stop cluster services on one node...
so only one node is available and tried to connect but cudn't login as sa....
September 3, 2007 at 12:59 pm
Something is connecting to the server before you do. Does this machine have a console? If so, could you disable the network and and restart SQL server from the command prompt. Then connect to it.
If you cannot get rid of the network, have a look at netstat.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/netstat.mspx?mfr=true
It can help you to figure out what other application/network is connecting to SQL Server.
Andras
September 3, 2007 at 6:39 pm
andras,
Yes I believe there is something connecting by default before I try to connect.....
I am not sure about the console....all I know is it is a 2 node cluster active-passive. and I tried all my efforts( bringing down and bringing back online, tried with sa account and services startup account ) on each node while other node's cluster services stopped and even thru the VIP address.
I see as the cluster services run under separate adminstrator account......does this any way logs into the server....I cannot stop this service becoz the cluster services has to run on atleast one node....( I stopped this service on the other node)...I dont have pwd for this account becoz it was used by windows group for the cluster installation.....
I did try the netstat -a.
I showed me the hostname:id( or some name I believe not the users) how do I identify which one is what and which one is going to sql server connection....
Thanks for your help so far andras....
September 4, 2007 at 12:01 am
You can go ahead and change the settings from the registry but be sure that you have taken a backup of the registry before you perform any specific operation.
Stop you SQL Agent
Stop your Full Text service in case you have.
September 4, 2007 at 7:58 am
Have you tried to connect from the console or only remote? Try to open an remote session on the console (mstsc /console) an then to connect to the server.
September 4, 2007 at 9:51 am
I recently had this issue with SQL 2005. I resolved it by disabling remote connections ( so that my application servers would not keep stealing the connection )
You can also set 2000 for local connections only by following the steps outlined here
September 4, 2007 at 11:06 am
You need to start the instance on a cluster via the command line, NOT the cluster administrator, to go into single user mode.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 5, 2007 at 8:52 am
Took out the -m option from the registry- sqlserver startup parameters.....
Can able to login smoothly....
September 5, 2007 at 12:50 pm
When I had this issue I went into EM, right mouse clicked on the database then left mouse clicked on Detach Database... I then forced the connection off by selecting "Clear" connections. I then selected Cancel (keeping the database attached). I was able to make my 1 connection and get what I needed to get done.
Might be easier than having to hack the registry to remove the start-up parameters.
Kurt
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply