Problem with database

GeoJoseph 46 Reputation points
2021-07-07T06:16:05.017+00:00

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]: /api/attachments/112386-1.png?platform=QnA

112300-1.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,776 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,292 questions
Accepted answer
  1. Seeya Xi-MSFT 16,441 Reputation points
    2021-07-07T08:55:41.847+00:00

    Hi @GeoJoseph ,

    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 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-07-07T08:43:58.233+00:00

    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?

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-07-07T13:08:05.873+00:00

    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

    1 person found this answer helpful.
    0 comments
  3. GeoJoseph 46 Reputation points
    2021-07-07T09:23:39.903+00:00

    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

  4. GeoJoseph 46 Reputation points
    2021-07-17T08:51:38.147+00:00

    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.