Timeouts in Microsoft SQL Server 2008 R2

Question

Timeouts in Microsoft SQL Server 2008 R2

kucyk

Mr or Mrs. 500

Points: 511
More actions
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?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

kucyk Mr or Mrs. 500 Points: 511 More actions · Answer 2

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]

kucyk Mr or Mrs. 500 Points: 511 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 4

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

Timeouts in Microsoft SQL Server 2008 R2

Cookies on SQLServerCentral