Post reply

DAC connection vs single-user mode

  • August 25, 2018 at 3:19 am

    #376769

    Are sqlcmd -A and SQLServr.exe -f doing the same job? What is the difference between Dedicated Administrator Connection and single-user mode in SQL Server?
    Thanks for your time!

  • August 25, 2018 at 6:52 am

    #2003314

    your SQL server could be unresponsive due to high CPU or something, but some memory is always reserved for the Dedicated Admin Connection(assuming it was enabled), so if a server seems unresponsive, you should be able to connect via DAC and start diagnosing. so in that scenario, your server could have multiple connections.

    dropping the server to single user mode means one and only one connection can connect, which you might do for maintenance and trouble shooting... so  the one connection is typically the sysadmin who just brought the server down and up in single user mode. an errant auto-connect process could intercept that single connection, or ObjectExplorer in SSMS, intellisense, third party plugins, etc  may grab that connection before your SSMS query window, which is why it's often suggested to connect via sqlcmd in those situations.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 6, 2018 at 5:41 am

    #2004677

    elea.grig - Saturday, August 25, 2018 3:19 AM

    Are sqlcmd -A and SQLServr.exe -f doing the same job? What is the difference between Dedicated Administrator Connection and single-user mode in SQL Server?
    Thanks for your time!

    the 2 are very different, starting sql command with the -A switch will simply allow an admin connection into the instance even in the event of an unresponsive instance.
    Starting sql server exe with -f flag starts the instance single user and minimal configuration
    It bypasses configured settings to allow the instance to start, helpful if someone has messed with settings and the instance fails to start normally

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 3 posts - 1 through 2 (of 2 total)

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