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 (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2008 – Download Microsoft SQL Server 2008 Express with Tools Free
Next Post
SQL SERVER – Technical Articles – Performance Optimizations for the XML Data Type in SQL Server 2005

Related Posts

56 Comments. Leave new

  • Epic WIN! works just perfect on sql 2008 r2, thanks for the help!!

    Reply
  • Worked a treat.

    Reply
  • 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

    Reply
  • Ben Nordlander
    October 19, 2011 8:02 pm

    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.

    Reply
  • 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.

    Reply
  • hi sir i have the -d,so what i do sir,please explain clearly

    Reply
  • thanks Pinal

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • You the man. Beat my head for 2 hours and in 3 seconds on your site I found out how to do it correctly.

    Reply
  • patelriki13ikin
    July 13, 2012 4:13 pm

    Thank u very much….
    its working fine….

    Reply
  • Alex Martinez
    July 19, 2012 1:39 am

    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.

    Reply
  • Alex Martinez
    July 19, 2012 1:47 am

    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)

    Reply
  • g2-902125243db7d5e697a20ae0137565ac
    November 24, 2012 1:25 am

    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

    Reply
  • g2-902125243db7d5e697a20ae0137565ac
    November 24, 2012 1:40 am

    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

    Reply
  • g2-902125243db7d5e697a20ae0137565ac
    November 24, 2012 2:08 am

    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

    Reply
  • g2-902125243db7d5e697a20ae0137565ac
    November 24, 2012 5:05 am

    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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version