locked
Reg : SQL Server unexpected shutdown RRS feed

  • Question

  • Hi All

    Greetings for the Day !!

    Today morning suddenly one of my client server got restarted and due to which the SQL server got stopped, while starting we are  getting error as

    "This error occurs when there is not enough space on the device to create the tempdb database."-- 1841 

    Even though my drive is having some 140 GB of space and we have started the SQL forcifully, it started and the TEMP DB path is showing some other drive and that is not there in server from starting.

    After logging to management studio after a force start, whenever we are trying to execute any query it is  throwing an error

     "Login failed for user ''hostname\administrator''. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) "....

    ** Waiting for your  valuable reply and it is highly appreciated as we are in urgency .

    Thanks in advance..


    ============ Regards Mani

    Tuesday, November 26, 2013 7:31 AM

Answers

  • Hello ,

    Your SQl server is in single user mode .And I am sure to recover from tempdb error someone put it in single user mode and forgot to revert.

    Can you check following

    Go to SQL server configuration manager ...SQl server services--RK on MSSQLserver service ..Go to advanced tab ...startup parameters...in that can you find any -m option if so remove it and restart SQL server service.

    See http://technet.microsoft.com/en-us/library/ms188236.aspx

    Now I would like to see 

    Sp_readerrorlog 1
    --
    sp_readerrorlog 2

    In both logs can you find something hich points to why SQl server restarted unexpectedly .Can you se eventviewer and widows logs at that time

    Check this link for various options to start SQl server

    http://technet.microsoft.com/en-us/library/ms180965(v=sql.105).aspx

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Tuesday, November 26, 2013 7:49 AM
    • Proposed as answer by Fanny Liu Wednesday, November 27, 2013 7:51 AM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 9:11 AM
    Tuesday, November 26, 2013 7:43 AM

All replies

  • Hello ,

    Your SQl server is in single user mode .And I am sure to recover from tempdb error someone put it in single user mode and forgot to revert.

    Can you check following

    Go to SQL server configuration manager ...SQl server services--RK on MSSQLserver service ..Go to advanced tab ...startup parameters...in that can you find any -m option if so remove it and restart SQL server service.

    See http://technet.microsoft.com/en-us/library/ms188236.aspx

    Now I would like to see 

    Sp_readerrorlog 1
    --
    sp_readerrorlog 2

    In both logs can you find something hich points to why SQl server restarted unexpectedly .Can you se eventviewer and widows logs at that time

    Check this link for various options to start SQl server

    http://technet.microsoft.com/en-us/library/ms180965(v=sql.105).aspx

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Tuesday, November 26, 2013 7:49 AM
    • Proposed as answer by Fanny Liu Wednesday, November 27, 2013 7:51 AM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 9:11 AM
    Tuesday, November 26, 2013 7:43 AM
  • You can have only one connection in single user mode. Make sure nobody else is connected (including Agent) and use SQLCMD instead of SSMS to do the SQL tasks you have ahead of you. SQLCMD don't try to open several connection, something that SSMS can do, depending on exactly how you use it.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, November 26, 2013 7:48 AM
  • >>>fter logging to management studio after a force start

    Did you add some parameter like -m  on the start.?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 26, 2013 7:49 AM
    Answerer