Post reply

SQLServer becoming unresponsive

  • September 23, 2011 at 7:58 am

    #257727

    Twice a day, SQLSERVER becomes unresponsive and I will have to stop TCP, SQLSERVER, and SQLSERVER Agent, then restart to connect to my sqlserver.

    OS=2008 R2 Server SP1

    SQL=2008 Enterprise SP2

    Error Log:

  • September 23, 2011 at 8:01 am

    #1385761

    Your post is cut off.

  • September 23, 2011 at 8:08 am

    #1385771

    Error Log

  • September 23, 2011 at 8:10 am

    #1385772

    Error [298] sqlserver Error: 258 TXP provider: Timeout error [258].

    Error [165] ODBC error: Login timeout expired

    Error [298] Sqlserver error: 258 Unable to complete login process due to delay in prelogin response

  • September 23, 2011 at 8:58 am

    #1385817

    Do you have login triggers?

  • September 23, 2011 at 9:16 am

    #1385834

    No, I don't have login triggers

    Shelley

  • September 23, 2011 at 9:28 am

    #1385848

    Can you define what unresponsive means? Are you sure it's the server and not your client?

    The inability to process a login usually means there are DDL triggers in place or a resource governor classifier that is delaying things. It could be memory as well. Is the server under load when this happens? Have you traced back through the error log before these messages to see what might be logged? Or maybe run a trace to track activity/performance monitor to track load before this happens?

  • September 25, 2011 at 5:55 am

    #1386199

    It sounds like something is pegging the resources. Do you have scheduled events in SQL Server? How about in the OS? Do you have any form of monitoring in place so that you can observe the behavior of the server over time, see what's happening before it becomes unresponsive?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 25, 2011 at 11:49 am

    #1386229

    what other software runs on this server?

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

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

  • September 25, 2011 at 8:09 pm

    #1386265

    Shelley Allen (9/23/2011)

    Twice a day, SQLSERVER becomes unresponsive and I will have to stop TCP, SQLSERVER, and SQLSERVER Agent, then restart to connect to my sqlserver.

    OS=2008 R2 Server SP1

    SQL=2008 Enterprise SP2

    Error Log:

    Does it happen at approximately the same time every day?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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