A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQL$XXXXX service.
Question
Wednesday, March 23, 2016 3:12 PM
Sometimes we have problem with SQL databases: applications lost connection to database and in application log we have message like this
Log Name: Application
Source: Report Server (XXXXXX)
Date: 23.03.2016 13:02:53
Event ID: 107
Task Category: Management
Level: Error
Keywords: Classic
User: N/A
Computer: XXXXXX
Description:
Report Server (XXXXXX) cannot connect to the report server database.
In the same time in system log there is this message:
Log Name: System
Source: Service Control Manager
Date: 23.03.2016 13:03:20
Event ID: 7011
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: XXXXXXX
Description:
A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQL$XXXX service.
What can be a problem? How to find out probable cause?
All replies (17)
Wednesday, March 23, 2016 3:27 PM
Kolya
Do you have long running reports?
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
Wednesday, March 23, 2016 3:30 PM
Is the server fully updated? sounds similar to the KB that kicked SQL authentication a year and a half ago.... around that time.
Wednesday, March 23, 2016 3:34 PM
No. I do not see any long reports. I run query "select @@servername" every 10 sec. When there is problem I will get response after 1-2 min.
Wednesday, March 23, 2016 4:01 PM
Hi Nicolay, does it work or happen the same in development/staging scenarios?
Wednesday, March 23, 2016 4:01 PM
select @@servername will give a name of the server, how does it relate to performance?
Make sure that Auto close is set to false for that database
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
Wednesday, March 23, 2016 4:41 PM
That is a generic error message and does not help diagnose the problem in any way.
Please look at the SQL Server log file to determine if there was an outage of some kind during that time. Otherwise, it could have been many different reasons from network connectivity to some other issue.
Thursday, March 24, 2016 12:05 AM
This is a common issue. all you need to do is increase the timeout.
Carefully follow the instructions in the below link and it works. I have done this in the past.
Good Luck
Kumar
Thursday, March 24, 2016 7:22 AM
Yes. The server was fully updated. It had update from 11.00.5522 to 11.00.6518.
Thursday, March 24, 2016 7:34 AM
It was working ok for couple months. But recently were made only updates for NIC and FC devices. Also now we have a lot ot error like this:
Log Name: Application
Source: Application Error
Date: 24.03.2016 9:59:42
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Computer: XXXXX
Description:
Faulting application name: mpclaim.exe, version: 6.3.9600.16384, time stamp: 0x5215f429
Faulting module name: MPIOUTILS.dll, version: 6.3.9600.17276, time stamp: 0x53e030ee
Exception code: 0xc0000005
Fault offset: 0x00000000000051a6
Faulting process id: 0x7ec4
Faulting application start time: 0x01d1859abbc24a51
Faulting application path: C:\Windows\system32\mpclaim.exe
Faulting module path: C:\Windows\system32\MPIOUTILS.dll
Report Id: fb25081e-f18d-11e5-80be-42f2e96533b7
Faulting package full name:
Faulting package-relative application ID:
Thursday, March 24, 2016 7:47 AM
Hello Uri.
The query "select @@servername" I used to understand whehe is problem.
All of databases have Auto close is set false.
Thursday, March 24, 2016 7:50 AM
Hello Tom Phillips.
SQL Server log file does not have any errors or problems.
Thursday, March 24, 2016 8:00 AM
Helo Kumar murra.
I have seen this link. But that is different problem. We have only problem with SQL Server service.
Thanks for your help.
Thursday, March 24, 2016 8:06 AM
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
Thursday, March 24, 2016 9:21 AM
Thank you Uri, I will ask our engineers to check server for your link.
Thursday, March 24, 2016 11:57 AM
Uri, the antivirus found nothing.
Thursday, March 24, 2016 1:01 PM
That would indicate some kind of network problem or a performance problem on the server at that time. I would check with your network people to determine if there was an outage before pursing other things.
Friday, March 25, 2016 9:52 AM
Hello Tom!
My network people do not see any problems with network. When I checked connection, I used Named Pipes protocol. My full script is:
goto start
:start
ping 127.0.0.1 -n 10 -w 1000 > nul
echo 1 before sqlcmd %date% %time% >> 1.txt
SQLCMD -h -1 -l0 -S np:\.\pipe\$\XXXXX\XXXXX\sql\query -Q "set nocount on;select @@servername"
echo 2 after sqlcmd %date% %time% >> 1.txt
goto start
When we have a problem, time period between "echo 1" and "echo 2" is 1-2 min.