SQL SERVER – Start SQL Server Instance in Single User Mode

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 (http://blog.SQLAuthority.com)

About these ads

56 thoughts on “SQL SERVER – Start SQL Server Instance in Single User Mode

  1. 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.

  2. Pingback: SQL SERVER - Restore Master Database - An Easy Solution Journey to SQL Authority with Pinal Dave

    • Hi,
      Actually in my sql server configration manager in advance tab there is no start parameter so can u please provide me any script for that.

      Thanks,
      Jolly

  3. 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”

  4. Pingback: SQL SERVER – Forgot the Password of Username SA Journey to SQL Authority with Pinal Dave

  5. @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.

  6. 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

  7. 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”.

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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.

  13. 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

  14. 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

  15. 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

  16. 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.

  17. 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

  18. i have a scenario

    my current OS is crashed and took sqlserver with it but luckly i saved the system database; Now i have installed the fresh copy of sql server with the same build and service pack;

    i want to replace the existing master database with the previous files (master.mdf and ldf) .. Actuly i am missing backup file , what i balieve that if i had master.bak file then i can easy over write the existing database and can restore to a previouse state but how can i do that with the MDF and LDF files

    hopefully i explain it!

    Talib Hussain

  19. I just wanted to thank you. I think i’ve been to this post about 5 times now and am now finally just copying in my notes so i don’t have to search for it everytime. Thank you again for this super helpful tip.

  20. Dude ! your a star I must have spent an hour trawling microsoft on how to do this ! yes i found the -m bit but where do i put -m, well done for putting it in terms a noob like me can understand rather than that microsoft waffle.

  21. I have changed the startup as single user by adding the “-m;” and now I am stuck in starting rhe SQL server. How do I start sqlserver from sqlcmd?

    I need to change the password of sa. Thats the requirement.
    Can anyone help?

  22. A trick for some of you.
    sqlservr -f”sqlcmd”
    This is like single-user mode in that in only allows connections from a particular application name. I’ve used it when I need to be the only one connected to the server and single-user mode keeps getting hogged by other connections.

  23. I am having a problem on SQL Server 2008R2 when following the instructions above and get this error ” Error 3(The system cannot find the path specified.) occurred while opening file ‘master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.”

    I tried removing the -m; at the startup Parameter to get back to where I was and now my server won’t start with the same messages reported above, and I tried rebooting the server as well. Any suggestions would be appreciated.

  24. Ignore my last post, I modified the startup parameter to be -m;-dC:\Program Files…..

    Not sure if that extra dash is 2008R2 specific, but I compared the startup parameters to another 200R2 instance it it was that way. (minus the -m; of course for the single user mode flag)

  25. Hello,
    I tried sqlcmd but I am still getting the same error:
    C:\Users\username>sqlcmd
    Msg 18461, Level 14, State 1, Server VOTSPRSTR2, Line 1
    Login failed for user ‘AD\usersname’. Reason: Server is in single user mode. Only one administrator can connect at this time.

    C:\Users\username>

    SQL Server Agent is not running only SQLServer is running from the services.msc view….

    Thanks,
    Dom

  26. Hello,

    I tried
    > sqlservr -m
    >sp_addsrvrolemember ”, ‘sysadmin’
    GO
    but it could not find the stored procedure… I think there should be something launch before!!!

    Thanks,
    Dom

  27. Hello,
    I restart all steps:
    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m

    2012-11-23 12:34:00.48 Logon Error: 18461, Severity: 14, State: 1.
    2012-11-23 12:34:00.48 Logon Login failed for user ‘AD\svcsprestore’.
    Reason: Server is in single user mode.
    Only one administrator can connect at this time. [CLIENT: 10.32.136.60]

    I am trying to get in single user mode to add a sysadmin account but it seems the command is trying more and it is looping for the last 10 minutes on the same message above!!!
    any idea?
    Thanks,
    DOm

  28. Hello,
    Finally trying again from scratch
    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net stop ms
    sqlserver
    The SQL Server (MSSQLSERVER) service is not started.

    More help is available by typing NET HELPMSG 3521.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net start m
    ssqlserver /m
    The SQL Server (MSSQLSERVER) service is starting.
    The SQL Server (MSSQLSERVER) service was started successfully.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>sqlcmd
    1> EXEC master..sp_addsrvrolememeber @loginame = N’AD\dominiqued’, @rolename = N’ sysadmin’
    2> GO
    1> exit

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net stop ms
    sqlserver
    The SQL Server (MSSQLSERVER) service is stopping.
    The SQL Server (MSSQLSERVER) service was stopped successfully.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>net start m
    ssqlserver
    The SQL Server (MSSQLSERVER) service is starting.
    The SQL Server (MSSQLSERVER) service was started successfully.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>

    Now I have access to the databases as sysadmin…
    Not sure what the issues were but try 4 times work…
    Thanks,
    Dom

  29. Okay, I had a similar problem except this was way worse. Imagine this:
    Cannot open configuration manager.
    cannot open the instance property
    cannot log into the database via sqlcmd

    what do you do then? forget it, install a new instance and move the database over to the new instance.

  30. Hello everybody,

    I need your help!!!! I am very frustrated doing lots of things to recover my sql server but it continues failing.

    My scenario is this: VMWARE –> VM: Win2012 Server, SQL Server 2008, standard instance –> MSSQLSERVER
    4 partitions in a basic disk. System, c(boot), e and f

    in c it is located master, model and msdb (mdf and logs)
    in e –> mdf for a DB called Portugal and Temddb.mdf
    in f –> the logs for databases

    What I did is to copy all the data from e and f, delete these volumen, extend c and create e and f again as separate disk.Then i paste all data of old e: in new e: and the same for f:

    I cannot start SQL Server (MSSQLSERVER) service. I get this log error:
    2013-01-24 09:33:12.20 Server Microsoft SQL Server 2008 (SP3) – 10.0.5512.0 (X64)
    Aug 22 2012 19:25:47
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (VM)

    2013-01-24 09:33:12.20 Server (c) 2005 Microsoft Corporation.
    2013-01-24 09:33:12.20 Server All rights reserved.
    2013-01-24 09:33:12.20 Server Server process ID is 3304.
    2013-01-24 09:33:12.20 Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
    2013-01-24 09:33:12.20 Server Authentication mode is MIXED.
    2013-01-24 09:33:12.20 Server Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
    2013-01-24 09:33:12.20 Server This instance of SQL Server last reported using a process ID of 36 at 24/01/2013 9:27:24 (local) 24/01/2013 8:27:24 (UTC). This is an informational message only; no user action is required.
    2013-01-24 09:33:12.20 Server Registry startup parameters:
    -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
    -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
    -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2013-01-24 09:33:12.21 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2013-01-24 09:33:12.21 Server Detected 4 CPUs. This is an informational message; no user action is required.
    2013-01-24 09:33:12.26 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
    2013-01-24 09:33:12.28 Server Node configuration: node 0: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2013-01-24 09:33:12.30 spid7s Starting up database ‘master’.
    2013-01-24 09:33:12.51 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = ‘MSSQLSERVER’.
    2013-01-24 09:33:12.55 spid7s SQL Trace ID 1 was started by login “sa”.
    2013-01-24 09:33:12.55 spid7s Starting up database ‘mssqlsystemresource’.
    2013-01-24 09:33:12.58 spid7s The resource database build version is 10.00.5500. This is an informational message only. No user action is required.
    2013-01-24 09:33:12.69 spid7s Server name is ‘CE-NAV02′. This is an informational message only. No user action is required.
    2013-01-24 09:33:12.69 spid11s Starting up database ‘model’.
    2013-01-24 09:33:12.84 spid11s Clearing tempdb database.
    2013-01-24 09:33:12.85 spid13s A new instance of the full-text filter daemon host process has been successfully started.
    2013-01-24 09:33:12.85 Server A self-generated certificate was successfully loaded for encryption.
    2013-01-24 09:33:12.86 spid11s Error: 5123, Severity: 16, State: 1.
    2013-01-24 09:33:12.86 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘F:\LOGS\TempDB\templog.ldf’.
    2013-01-24 09:33:12.86 spid11s Error: 5123, Severity: 16, State: 1.
    2013-01-24 09:33:12.86 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘E:\Data\TempDB\tempdb.mdf’.
    2013-01-24 09:33:12.86 Server Server is listening on [ ‘any’ 1433].
    2013-01-24 09:33:12.86 Server Server is listening on [ ‘any’ 1433].
    2013-01-24 09:33:12.86 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2013-01-24 09:33:12.86 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2013-01-24 09:33:12.86 Server Server is listening on [ ::1 1434].
    2013-01-24 09:33:12.86 Server Server is listening on [ 127.0.0.1 1434].
    2013-01-24 09:33:12.86 Server Dedicated admin connection support was established for listening locally on port 1434.
    2013-01-24 09:33:12.86 spid14s Starting up database ‘ReportServer’.
    2013-01-24 09:33:12.86 spid15s Starting up database ‘ReportServerTempDB’.
    2013-01-24 09:33:12.86 spid16s Starting up database ‘Portugal’.
    2013-01-24 09:33:12.86 spid13s Starting up database ‘msdb’.
    2013-01-24 09:33:12.87 spid14s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid14s FCB::Open failed: Could not open file E:\Data\Data\ReportServer.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid15s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid15s FCB::Open failed: Could not open file E:\Data\Data\ReportServerTempDB.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid14s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid14s Unable to open the physical file “E:\Data\Data\ReportServer.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid15s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid15s Unable to open the physical file “E:\Data\Data\ReportServerTempDB.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file E:\Data\Data\Portugal.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid14s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid14s FCB::Open failed: Could not open file F:\LOGS\ReportServer_1.LDF for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid15s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid15s FCB::Open failed: Could not open file F:\LOGS\ReportServerTempDB_1.LDF for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid14s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid14s Unable to open the physical file “F:\LOGS\ReportServer_1.LDF”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid14s Error: 5105, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid14s A file activation error occurred. The physical file name ‘F:\LOGS\ReportServer_1.LDF’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
    2013-01-24 09:33:12.87 spid15s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid15s Unable to open the physical file “F:\LOGS\ReportServerTempDB_1.LDF”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid15s Error: 5105, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid15s A file activation error occurred. The physical file name ‘F:\LOGS\ReportServerTempDB_1.LDF’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
    2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file E:\Data\Data\Portugal_1.ndf for file number 3. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal_1.ndf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 9.
    2013-01-24 09:33:12.87 spid16s Unable to open the physical file “E:\Data\Data\Portugal_1.ndf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid16s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid16s FCB::Open failed: Could not open file F:\LOGS\Portugal_2.ldf for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.87 spid16s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.87 spid16s Unable to open the physical file “F:\LOGS\Portugal_2.ldf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.87 spid16s Error: 5105, Severity: 16, State: 1.
    2013-01-24 09:33:12.87 spid16s A file activation error occurred. The physical file name ‘F:\LOGS\Portugal_2.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
    2013-01-24 09:33:12.91 spid11s Error: 17204, Severity: 16, State: 1.
    2013-01-24 09:33:12.91 spid11s FCB::Open failed: Could not open file E:\Data\TempDB\tempdb.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
    2013-01-24 09:33:12.91 spid11s Error: 5120, Severity: 16, State: 101.
    2013-01-24 09:33:12.91 spid11s Unable to open the physical file “E:\Data\TempDB\tempdb.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.
    2013-01-24 09:33:12.91 spid11s Error: 1802, Severity: 16, State: 4.
    2013-01-24 09:33:12.91 spid11s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
    2013-01-24 09:33:12.91 spid11s Error: 5123, Severity: 16, State: 1.
    2013-01-24 09:33:12.91 spid11s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘E:\Data\TempDB\tempdb.mdf’.
    2013-01-24 09:33:12.91 spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
    2013-01-24 09:33:12.91 spid11s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
    2013-01-24 09:33:13.14 Logon Error: 17187, Severity: 16, State: 1.
    2013-01-24 09:33:13.14 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: fe80::759f:e712:daa1:5f25%12]
    2013-01-24 09:33:13.17 Logon Error: 17187, Severity: 16, State: 1.
    2013-01-24 09:33:13.17 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: ]

    As you can see there are lots of errors …

    any one can help me? Thank you so much.

    best regards!!
    NACHO

  31. it is solved! Thank you anyway. i have restored backups from databases that were not system’s backups and give rights into the e : and f: volume to allow access from sql to tempdb.

    Best Regards,
    NACHO

  32. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  33. Hi,

    I’m not able to change the start up parameter in MSSQL 2012 server, getting error as “The Parameter is Invalid”. Not getting where I’m wrong here, earlier several times I had tried this.

    Parameter :

    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf

    Please help me.

  34. Hi Dave just wanted to say thanks a million for all the incredible advise you always give to the SQL community. I know this comment is 2 years after the original one but i just wanted to make a comment on this.
    1. after the server is started in single user mode make sure all other sql services like reporting services etc. are stopped or it will tell you that another user is already connected.
    2. using SQLCMD -S from the command prompt I found is easiest for me to connect then i just use TSQL commands to fix the issues

  35. Thanks for your wonderful post. Unfortunately, I run into some problem. I have a few Server Instances on the same server. Not sure it was the culprit or not. However, after adding -m; into the Startup Parameters and restarted successfully, I am not able to connect to the database server using SQLCMD. Is there anything I need to check ?

  36. Hi,
    If in Advance Tab- Startup Parameters box contain is disabled,then how to enabled it ??

    Please replay.
    Thanks in Avvance.

    Ajit

  37. If you’re receiving an error from sqlcmd similar to “”Login failed for user ‘sa’. Reason: Server is in single user mode. Only one administrator can connect at this time.” and have an error message similar to “Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ ” in the event viewer after you start the service this link might solve your problem: http://support.microsoft.com/kb/2163980

    Chased this around for hours before figuring it out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s