question

EldhosePKuriakose-5238 avatar image
0 Votes"
EldhosePKuriakose-5238 asked SeeyaXi-msft edited

Problem with database

I have a database hosted in server, there is 2 issues I am facing.

  1. My web application is getting the error "Execution Timeout expired" occasionally, Its for a simple delete query. Some times it will work just fine, some times this error will popup.

  2. Other issue is, after I restore that database to my local system I will not able to open "NEW QUERY" in management studio. When I click "New Query" the login prompt for "SQL Management Studio" will be shown. After clicking login, sometimes it will open the "New Query", Some times it wont.

Please help me.
Image of login prompt attached


![112386-1.png][2] [2]: /answers/storage/attachments/112386-1.png

112300-1.png


sql-server-generaldotnet-csharp
1.png (135.1 KiB)
1.png (136.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EldhosePKuriakose-5238 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

Hi ,
Sorry for the delay, I am working on it and I will do "Accept Answer" once I get a resolution.

0 Votes 0 ·
SeeyaXi-msft avatar image SeeyaXi-msft EldhosePKuriakose-5238 ·

Hi @EldhosePKuriakose-5238 ,

Is there any progress on this issue? If there are any further questions, please let us know.

Best regards,
Seeya

0 Votes 0 ·
SeeyaXi-msft avatar image
1 Vote"
SeeyaXi-msft answered ErlandSommarskog commented

Hi @EldhosePKuriakose-5238,

problem 1

A timely solution is to increase the timeout.
According to your discription, follow this: sqlcommand.CommandTimeout = 120;
The default value of SqlCommand.CommandTimeout is 30 seconds, you don't need to set 120, or you can set other suitable values.

This type of timeout can have three causes.
1.There's a deadlock somewhere
2.The database's statistics and/or query plan cache are incorrect
3.The query is too complex and needs to be tuned, which can be excluded with your description.

A deadlock can be difficult to fix, but it's easy to determine whether that is the case. Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics: exec sp_updatestats
If that doesn't work you could also try: dbcc freeproccache
You should not do this when your server is under heavy load because it will temporarily incur a big performace hit as all stored procs and queries are recompiled when first executed.

BTW, as for the third issue, if you do a query about table, you should better create a index on it for better performance.

problem 2

The connection is automatically disconnected because the database has not been operated for too long.
You can reconnect after the link is broken, which is a fault-tolerant method of the program.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This type of timeout can have three causes.
1.There's a deadlock somewhere


No, deadlocks rarely cause execution timeouts, as SQL Server checks for deadlocks for at least every five seconds, and will inject an error one of the processes in the deadlock. Therefore, with a default timeout, you will in most cases get the deadlock error before the timeout error.

Regular blocking can indeed be the reason for execution timeout, and I also mentioned this in my Answer. But a deadlock is a different thing.



0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

This seems to be two unrelated questions.

For the first question, the error "Execution timeout expired" is an error from the client API, not from SQL Server. Most client APIs for SQL Server have a default timeout of 30 seconds, meaning that the API gives up, if SQL Server has not completed the query for 30 seconds. So if you have a query that takes 25-35 seconds to complete, you will sometimes get a timeout, and sometimes not. The query timeout is a property on the SqlCommand object.

If the query usually completes immediately, but sometimes takes more than 30 seconds, one possible reason is blocking, although there are other possibilities as well.

As for the second question, I am not sure that I understand. When it does not open the window, do you get an error message or what happens?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EldhosePKuriakose-5238 avatar image
0 Votes"
EldhosePKuriakose-5238 answered ErlandSommarskog commented

Thanks a lot for you replies. I just want to attach a screenshot of the 2nd issue, when I try to open the "NEW QUERY" just after the login it is giving me the login box, after I click "CONNECT" its showing another message as in the image below.
112506-2.png



2.png (132.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't think I've seen that error message before.

You say that it occurs after you have restored your database. If you wait for a while, are you able to connect then?

As Tom says, apply the latest Cumulative Update for SQL 2017, which I think is CU24. I would not really expect the CU to resolve the issue, but there is no reason running with an outdated version.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

In addition to the other replies, you are running the RTM version of SQL 2017. I HIGHLY recommend updating to the current CU. There have been many changes in 4 years since that release.

https://support.microsoft.com/en-us/topic/kb4047329-sql-server-2017-build-versions-346e8fcd-c07c-5eeb-e10b-e3411ba8d8dd

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EldhosePKuriakose-5238 avatar image
0 Votes"
EldhosePKuriakose-5238 answered SeeyaXi-msft edited

Thank you all for helping me figure this one. I got the solution for the 1st issue of "Application time out for a simple query". It took some time to figure out, but got it.
The issue was I have a form that is taking so long to complete its execution, with complex calculations and queries.
When that form transaction is begun, other form's will be going to "Time Out Expiry". That's the reason for first issue.
Second issue I didn't find out the solution, so I will keep looking and update if I get a solution.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EldhosePKuriakose-5238

If you encounter any trouble during the resolution process, please feel free to tell us.
And have a nice day!

Best regards,
Seeya

0 Votes 0 ·