Post reply

Timeouts in Microsoft SQL Server 2008 R2

  • February 14, 2017 at 5:24 am

    #327281

    Hello,
    my db server is running ok but... sometimes (a few times per day) I have issues with timeouts in app server and db server during backups or others mp like update stats, checkdb etc.

    Environment:
    APP server (oledb) <----> db server (without firewalls)

    SSCM config:

    services:
    browser online
    mssqlserver online @ 1433 port

    protocols:
    shared memory online
    name pipes online
    tcp online
    via disabled

    ++ with one alias (previous dba configured it, i don't know why...):
    alias myserver via tcp protocol to myserver

    error message in app server:
    Database error:[Microsoft][ODBC SQL Server Driver]Login timeout expired

    sometimes (2 times KO, 1 OK for example: monday error, tuesday error, wednesday with success) we have similar problem in sqlserver instance with jobs - error message

    Executed as user: myserver\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  8:00:01 PM  Could not load package "Maintenance Plans\MP" because of error 0xC0014062.  Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired).  The SQL statement that was issued has failed.  Source:   Started:  8:00:01 PM  Finished: 8:00:17 PM  Elapsed:  16.006 seconds.  The package could not be loaded.  The step failed.

    SQL version:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) [/li]

  • Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    ODBC conf:

    Microsoft SQL Server ODBC Driver Version 06.01.7601

    Microsoft SQL Server ODBC Driver Version 06.01.7601
    Data Source Name: myconnectionData Source Name: myconnection
    Data Source Description: my_test_exampleData Source Description: my_test_example
    Server: myserverServer: myserver
    Database: mydatabaseDatabase: mydatabase
    Language: (Default)Language: (Default)
    Translate Character Data: YesTranslate Character Data: Yes
    Log Long Running Queries: NoLog Long Running Queries: No
    Log Driver Statistics: NoLog Driver Statistics: No
    Use Regional Settings: NoUse Regional Settings: No
    Prepared Statements Option: Drop temporary procedures on disconnectPrepared Statements Option: Drop temporary procedures on disconnect
    Use Failover Server: NoUse Failover Server: No
    Use ANSI Quoted Identifiers: YesUse ANSI Quoted Identifiers: Yes
    Use ANSI Null, Paddings and Warnings: YesUse ANSI Null, Paddings and Warnings: Yes
    Data Encryption: No Data Encryption: No

    I was trying to run sql profiler to trace timeouts but there wasn't any issues during when app server got timeouts.
    We have AV TrenMicro with SQL polices (exlusion for sql process and file types: mdf, ndf, bak etc)SQL version:ODBC conf:I was trying to run sql profiler to trace timeouts but there wasn't any issues during when app server got timeouts.We have AV TrenMicro with SQL polices (exlusion for sql process and file types: .mdf, .ndf, .ldf, .bak etc)

    How can I avoid and repair timeouts issues?

  • February 14, 2017 at 6:25 am

    #1928334

    Timeouts are defined by the connection. You won't find them on the DB server, you need to capture them in the App server which is the one getting the timeouts.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 14, 2017 at 7:26 am

    #1928361

    Luis Cazares - Tuesday, February 14, 2017 6:25 AM

    Timeouts are defined by the connection. You won't find them on the DB server, you need to capture them in the App server which is the one getting the timeouts.

    sure, thank you!
    As you can see I have some timeouts in sql logs from sql agent - jobs like update stats or checkdb.

    [...] LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). [...]

    moreover... for example checkdb - first time failed with oledb errror, the next one with success
    [/url]

  • February 15, 2017 at 4:02 am

    #1928579

    Is there any app to trace connection errors with issue details etc?

  • February 17, 2017 at 7:29 am

    #1929103

    Connection timeouts are very hard to track. They're almost impossible to track within SQL Server because they're not connecting, therefore, SQL Server doesn't see anything. You just can't track what's not there. Generally, you'll need to track this issue from the outside, network management tools, etc. I don't have a good, specific, suggestion on it.

    "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

    • Viewing 5 posts - 1 through 4 (of 4 total)

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