Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Question

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

sam 55243

SSCrazy

Points: 2697
More actions
February 15, 2016 at 1:13 am

#146020

Hi All,
Im getting error: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." for one of the stored procedure from the application. This happens only for specific time duration between (02:00 Am to 02:30 AM) rest of time the same stored procedure runs fine.
On checking the database logs there is no specific deadlock/error issues captured in it. In fact there is a third monitoring tool for this database and it didn't report any kind of issue during that period.
We enabled SQL Server profiler trace to analyze the issue further and from the attached file (capture.png) it seems that the above SP was running for 19 min and then stopped with Error column set to value of '2' (2 - Abort).Adding to this we did try to execute the same stored procedure with same parameters from SSMS and it went successfully (Attached image capture1.png)
From the above analysis so far I assume this is not a database issue might be some thing else which I'm missing right now.
Or is there anything else I can do from database side to fix this.
Please provide your inputs.
Thanks
Sam

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You haven't posted the stored procedure definition, but I'm guessing it connects to a remote server, maybe using a linked server. Perhaps 2:00 to 2:30 am is when index maintenance or some other blocking process takes place on the remote server?

John

sam 55243 SSCrazy Points: 2697 More actions · Answer 2

Hi John,

There are no remote server here. The SP refers to a table that is present in another database on the same server.

While running trace on the server I did enable 'Blocked process report', 'Deadlock Graph event' and there was no report for the event in the final trace log file. This issue happens intermittently.

We do get this time out error at least thrice or some times entire week.

Not sure what might be the exact reason as I didn't get any specific error in trace log except that the stored procedure was running for quite long before it was aborted (as shown in earlier attachment)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's no error in the log, because it's not a SQL error. A timeout is a client-side error. The client decides that it's waited too long for the query to complete and tells SQL to abort execution.

It's the same as manually pressing the 'stop' button in Management Studio.

The default .net timeout is 30 seconds. If the procedure is taking longer than that, you'll need to either increase the timeout or tune the procedure so that it's running faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sam 55243 SSCrazy Points: 2697 More actions · Answer 4

Hi,

I executed the Stored procedure with same set of parameters that is passed from client application in SQl Management studio and it executed in few seconds. I didn't get any time out error. And I don't think query tuning is required here.

I checked in the app config there the connection time out is set to 600 and command time out is set to 1200 respectively.

And in application error log most of time we get error logged like ' Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.' And in few cases we do get connection time out error.

One more thing is that there is clustering done on this server. 'IsClustered' is 1 for this server. Total eight nodes are supported.

Not sure what might be actual cause for this issue. Please advice.

Thanks

Sam

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No, the SQL Server being clustered is not the problem.

A timeout occurs when a query/stored procedure call takes longer than the command timeout is set to, and so the application tells SQL Server to abort the operation.

Management studio query window won't timeout, it has infinite timeout, and it's not uncommon to have queries running in different times in SSMS vs application due to different session settings

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sam 55243 SSCrazy Points: 2697 More actions · Answer 6

Thanks Gail for your suggestion.

Now if i had to ascertain the exact reason as to why the query takes so much time to complete the execution only for specific time (between 02:00 Am to 02:30 AM when this time out issue happens daily) how can i get the query plan from cache to analyze this further and to do necessary tuning. Or What made SQL optimizer to use bad execution plan. How to investigate that?

I dont want to increase the time out from .NET without knowing the exact reason for this issue.This will be kind of temporary fix and it will be very difficult to come to exact figure.

Please share your input.

Thanks

Sam

sam 55243 SSCrazy Points: 2697 More actions · Answer 7

After running server side trace I was able to detect the SQL:statement that was taking too much time to execute and due to time out error was happening.

Next I need to fine tune that.