Tempdb stopped SQL Service

  • Hi All,

    I had an incident this morning that I haven't seen before regarding the tempdb.

    When I got to the office I saw one of my production servers SQL Service was stopped.

    I started it immediately and started to go through the log files and event viewer.

    The SQL Logs did not give much, I found this below error 1000's of time's but the description does not give me a lot of information:

    Date2011/12/08 04:35:11 AM

    LogSQL Agent (Archive #1 - 2011/12/08 04:45:00 AM)

    Message

    [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]

    In the event viewer just before the SQL Service was stopped I found 1000's of these errors that are exactly the same.

    There is insufficient system memory to run this query.

    After all of them I found this error below:

    During undoing of a logged operation in database 'tempdb', an error occurred at log record ID (5471:265598:506). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

    Then immediately after that error I got this one below:

    SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

    I then got a informational message from SQL:

    SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    This specific one doesn't really bother me as I know it was caused by the service that was shutdown.

    Then I received this below error twice in a row:

    SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: <local machine>]

    Do you have any idea where I can begin to prevent this from happening again? And why this happened exactly?

  • Google is your friend

    This blog describes a similar case, including their solution: http://www.sqlmusings.com/2011/06/25/connection-error-233-no-process-at-the-other-end-of-the-pipe/[/url]

    _____________________________________________________
    Do not go past the mark you aimed for, but learn when to stop.

    You can find me on LinkedIn.
    I support The Programmer's Bill of Rights.

    MCITP, MCDBA, MCSD

  • Thank you, I tried to google the error but did not get too far... 😉

  • Hi Erwin,

    The named pipes was enabled and the firewall settings are correct (the firewall is disabled on this server) this server have been running for quite some time.

    But the sudden errors with the insufficient memory problem is what is bothering me, because I think(correct me if I am wrong) that is what caused the tempdb to bomb out and stopped the SQL Service.

  • OK, it was worth a shot of course.

    Do you know if there was a big query running at that time or is there maintenance scheduled that could have caused this?

    Have you checked the Windows Event log for possible causes around that time?

    Maybe this KB article applies: FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005

    _____________________________________________________
    Do not go past the mark you aimed for, but learn when to stop.

    You can find me on LinkedIn.
    I support The Programmer's Bill of Rights.

    MCITP, MCDBA, MCSD

  • Hi Erwin,

    There was big queries running, but they run everyday.

    There was nothing new or changed on the server.

  • Nothing in the eventlogs indicating hardware/network problems?

    Have you had a chance to look at that KB article from Microsoft?

    _____________________________________________________
    Do not go past the mark you aimed for, but learn when to stop.

    You can find me on LinkedIn.
    I support The Programmer's Bill of Rights.

    MCITP, MCDBA, MCSD

  • Hi,

    I recently has the same issue ( insufficient memory) on the active / active cluster ( with 2 instances on each node).

    When all the four instances are running on one node, i faced these msg in the event log during execution of Database maintenance jobs ( esp reindexing ...)

Viewing 8 posts - 1 through 7 (of 7 total)

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