Timeout Expired. the Timeout Period Elapsed Prior to Completion of the Operation or the Server Is Not Responding. the Statement Has Been Terminated

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

Looks like you have a query that is taking longer than it should.
From your stack trace and your code you should be able to determine exactly what query that is.

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

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.
However, since you state the issue occurs sometimes, and the stack trace indicates your application is starting up, I think you're running a query that is only run on occasionally. You may be better off by forcing SQL Server not to reuse a previous query plan. See this answer for details on how to do that.

I've already touched on the third issue, but you can easily determine whether the query needs tuning by executing the query manually, for example using Sql Server Management Studio. If the query takes too long to complete, even after resetting the statistics you'll probably need to tune it. For help with that, you should post the exact query in a new question.

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

If your query needs more than the default 30 seconds, you might want to set the CommandTimeout higher. To do that you'll change it after you instantiated the DataAdapter on the SelectCommand property of that instance, like so:

private void FillInDataGrid(string SQLstring)
{
    string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString; //hier wordt de databasestring opgehaald
    DataSet ds = new DataSet();
    // dispose objects that implement IDisposable
    using(SqlConnection myConnection = new SqlConnection(cn))
    {
        SqlDataAdapter dataadapter = new SqlDataAdapter(SQLstring, myConnection);

        // set the CommandTimeout
        dataadapter.SelectCommand.CommandTimeout = 60;  // seconds

        myConnection.Open();
        dataadapter.Fill(ds, "Authors_table"); 
    }
    dataGridView1.DataSource = ds;
    dataGridView1.DataMember = "Authors_table";
}

The other option is to address your query. In Sql Server you can analyze the execution plan. I bet there is a full-table scan in it. You might experiment with adding an index on one or two columns in your [old] and [new] table. Keep in mind that adding indexes comes at the cost of higher execution times for inserts and updates and space requirements.

Related Topics