There are certain situation when user wants to start SQL Server Engine in “single user” mode from the start up.
To start SQL Server in single user mode is very simple procedure as displayed below.
Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.
Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.
Make sure to remove newly added params after required work is completed to restart it in multi user mode.
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
You can also run it from the command line. Also, you can still connect via SSMS. You just have to remember you get only one question. So don’t let the Explorer connect. Connect exclusively using a new query.
It says ” The request failed or the service did not repond in a timely fashion.Consult the event log or other application error logs for details”
Do not add a space between the semicolon and the rest of the command line
so not : “-m; -dc…” but “-m;-dc….”
We can use—
sp_dboption ‘Dbname’,’single user’,true
@Atin,
Sp_dboption is to set one database in single user mode.
Here we are talking about starting whole sql server instance in single user mode.
~ IM.
I changed the sql server to single mode and then tried to connect the server using “sqlcmd” but i got some error.
Please give me solution.
” Msg 18461 Level 1 14, State 1, Server Mungalpara, line
Msg 18461,Level 16 State 1
“Login failed for user ‘sa’.”
“Reason: Server is in single user mode. Only one administrator can connect at this time”.
Hello Haritkumar,
connect using sqlcmd instead of SSMS. Make sure SQL Serever Agent service is stopped and if requried block the sql server port to block user requests.
Regards,
Pinal Dave
Pinal,
You always have answers!! How do I block the sql port to user requests?
HI,
I m also getting the same Error for Single user …now i want to allow multiple user what i have to do ??
Regards
Daxesh Paneliya
Thanks Pinal, you’re a legend.
It’s the smallest things, in this case a semicolon, that make all the difference.
Thanks Pinal, yes the semicolon REALLY helped!!!
Thank you
Worked a treat.
Hi,
Iam unable to start MSSQLSERVER .
I try from command promt then gives me error no : – 3417
How start service ?
if problem in master database how to recover it ,as my service is not running ?
waiting for reply
Thanks In Advanced
i have started sqlserver in single user mode by typing sqlservr.exe -m
it worked
but
sqlcmd does not start
yes if i start mssqlserver service then sqlcmd worked
also
lines
restore database master from masterbackup (backupdevice)
go
it says start your sql server in single user mode
please help
I have a stored procedure where we set the DB into single user mode before running another set of stored procedures.
I am able to set it into single user and restore it back to multi user.
But we have issues here.
After the DB is set to single user and when we connect to the database it doesn’t allows us to connect which is fine but then it stops the process that is to happen when the database is in single user mode.
Can some one please give me a solution or explanation for this.
Greetings,
I have tried following your process several times. For some reason, I can not get sqlcmd to open. I’ve also tried recommended procedures from microsofts technet and other blogs. I have no problem with getting the server to come up in single user mode. But, when I attempt to then run sqlcmd I either get
“HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd Error: Microsoft SQL Native Client : Communication Lnk Failure.”
And in my Application Event Log I have an error that says “Login failed for user Reason: Server is in single user mode. Only one adminstrator can connect at this time. [CLIENT: local machine]
or, I just get the login failed for user…. error, depending on what method I use to start the server in single user mode.
Please, what am I doing wrong?
Thanks in advance
Wolf
Hi Pinal ,
In one of production environment at customer site , we are getting error message “Log File not available for TEMPDB” and now team has taken a SQL Server 2005 Database Engine Service restart before taking proper cure steps.
After Database engine restart its not starting up and now neither SQL Database engine service is getting restarted nor allowing anything to do , have tried starting database engine service in single user mode with -m , facing the same issue.
Need your suggestion what can be done in this case.
Kind Regards
Kuldeep
Hello everyone,
Today I faced same issue as Wolf, even is late for him to use this (I’m sure he got a solution to fix it), I think is useful to know that , when sql is started in single user mode, you have to connect to the machine hosting the instance of SQL Server using Local Admin account, not Domain account (even it is in Administrators Group) in order to acces SQL.
Hope it helps.
Mira
Yay! Thank you, well explained. Forgot the semicolon :)
Hi Everyone,
I can’t able to start the service from Configuration Manager, but able to start service in single user mode through command ptompt, but aftrer that to run a Query from SQLCMD i am unable tp connect to SQLCMD, it’s giving me only one user can connect now, DB is in sigle user mode.
HOw to connect to SQLCMD and i am using sysadmin accoutn on the system as wel..
Any how i have opened case with Microsoft, i ll update once i got the solution. i cant wait for on Google this time.
If any one have quick comments please let me know.
OK – so how does this work when you are trying to access a SQL server instance on a domain controller (such as those used for Sharepoint on Small Business Server 2011) as you don’t have the local machine account :(
Thanks
Mark