November 5, 2008 at 4:18 pm
Hi everyone...
Having a bit of a nightmare with this issue...
Need to run SQL 2005 in single user mode to restore a master database. We have several servers and have been through this process several times already on other servers without issue.
When I start SQL in single user via the cmd line, I get the usual text displaying the various checks it does on startup - but then, twice per every second - a failed login attempt is displayed:
Error: 18461, Severity: 14, State: 1.
2008-11-05 22:33:10.25 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: ]
This makes it impossible to do anythign in the cmd prompt other than ctrl c / y to shut it down as a new login attempt is displayed every 0.5 seconds.......
I've tried everything google seems to have to offer - turned off anything SQL like in Config Mgr, stopped the default trace.....
Am all out of ideas - anyone seen this before?
Thanks,
Paul
November 5, 2008 at 4:36 pm
Check for any application services that are trying to reconnect to SQL Server. Disable those services to prevent them from trying to log in.
However, are you sure you need to be SQL Server in single user mode? Or, do you really only need that database in single user mode?
If the latter, use:
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
perform whatever tasks you need...
ALTER DATABASE db SET MULTI_USER;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2008 at 4:40 pm
Thanks - I'll check it....
I'm trying to restore the master db - which I thought had to be done in single user mode?
November 5, 2008 at 4:48 pm
Yes, that is correct - if you are restoring master then SQL Server needs to be in single user mode.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2008 at 4:51 pm
ok - I can't see any application services that would need to be connecting to SQL server..... I'm stumped.
November 5, 2008 at 7:53 pm
Those services don't have to be on that server - can be on the web server or a separate application server. If you are seeing connection attempts every few seconds - there is definitely some process, somewhere, trying to connect.
Until you can get that service disabled - you are going to have a problem getting your server into single user mode and grabbing that connection.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2008 at 1:21 am
Finally resolved......
As usual, pretty simple solution. Turns out it was some kind of errant trace - even though I had thought I disabled the default trace with sp_configure.....
instead of using sqlservr.exe -m I used sqlservr.exe -m -T0 to disable the trace on startup and hey presto! restoration heaven.....:w00t:
December 6, 2013 at 2:17 am
There is a small trick which can be used http://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only-one-administrator-can-connect-at-this-time/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply