Problems with single user mode

Question

Post reply

Problems with single user mode

fminns-1058143

Default port

Points: 1404
More actions
December 3, 2009 at 2:46 am

#136443

Hi all
OK, so I have added the -m option to the startup params and go the database to come up in single user mode.
But now, I'm getting the following error and no idea why?
I have made sure that remote connections are allowed and that TCP/IP and names pipes is also (not sure if this is necessary but it was a suggestion I found elsewhere that didn't work).
The error is as follows.
Any ideas?
Regards
Farren
Cannot connect to OXF-SQLLAB1\SQLLAB12005.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

fminns-1058143 Default port Points: 1404 More actions · Answer 1

Additionally, I sometimes get the error that only one admin can log on.

However, even when I just get the error in the prev post, the log still caontains the following :-

Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

What on earth is going on. I have made sure that the only service running is the SQL Server itself. All other services are shut down.

Cheers

Farren

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Sounds like named pipes isn't set up. I wouldn't use it personally. TCPIP should be about everything you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fminns-1058143 Default port Points: 1404 More actions · Answer 3

I have tried everything.

No matter what I do, and no matter how many other processes I kill off I still always get the same message telling me that only one administrator can connect at a time.

I'm now just trying to connect from the command line...

sqlcmd -S 172.29.150.23,2434

What am I missing?

I'm still new to SQL and assumed this would be a very simple matter.

Farren

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

fminns-1058143 (12/3/2009)
I have tried everything.
No matter what I do, and no matter how many other processes I kill off I still always get the same message telling me that only one administrator can connect at a time.
I'm now just trying to connect from the command line...
sqlcmd -S 172.29.150.23,2434
What am I missing?
I'm still new to SQL and assumed this would be a very simple matter.
Farren

If you've got the database in single user mode... you only get one connection. Better would be to set it to restrictured user mode, then only sysadmin's can connect and you can get more than one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fminns-1058143 Default port Points: 1404 More actions · Answer 5

Hi Grant

But who/what is using up the other connection?

The reason I want single user mode is because I want to try testing a restore of the master database which I assume restricted mode would be no good for ?

Regards

Farren

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

fminns-1058143 (12/3/2009)
Hi Grant
But who/what is using up the other connection?
The reason I want single user mode is because I want to try testing a restore of the master database which I assume restricted mode would be no good for ?
Regards
Farren

It could be anything, but once a single connection slips in ahead of you... game's over.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fminns-1058143 Default port Points: 1404 More actions · Answer 7

Just wanted to update this in case anyone else has a similar issue.

Basically what was happening was that a default tracing job was jumping in before I could log on, so I disabled it with:-

sp_configure 'default trace enabled',0

exec sp_configure

Then stopped/started in single user mode.

sqlservr.exe -c -m -s {INSTANCE NAME}

And all was well.

Also had to make sure that when in Management Studio, I did not initaially click on connect, but instead on New Query. After that, I could restore the master db from the command line and everything worked fine.

Regards

Farren