stored procedure for multithreaded app - Timeout expired. The timeout period elapsed prior

I have an application using taskfactory that creates multiple tasks which insert records into the same table in a database. I have jobs that cleanup the table when it gets too large and have the execution time-out set to 0 in options in sql server management studio. I keep getting the following error on the tasks when 开发者_JAVA百科trying to do an insert using a stored procedure and set an exception handling process to sleep and try again. This does not help as it seems it timeouts on almost every attempt. Not sure how to handle this and ensure the table handles all these inserts. Tried including connect timeout = 600 in one of the processes connectionstring but this did not help.

Also set sqlquerytimeout value in registry to 90 but still not helping.

Appreciate responses.

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

could not see the details of the error since visual studio 2010 debug options had to be set that way, but I will set a responsestream and capture

but I rebooted sql server and somehow everything is fine now...not sure why.

Errors have reoccurred.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
     Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,  
     Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, 
     SqlCommand cmdHandler, SqlDataReader dataStream, 
      BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior 
     runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior 
     cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior 
   cmdBehavior, RunBehavior runBehavior, Boolean returnStream, 
     String method, DbAsyncResult result)  
  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, 
     String methodName, Boolean sendToPipe)
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 at GNIPProcess.Feeds_DB.insert_tfeed(String data, String searchTerm, 
  String entryId, String entryContent, String pubDate, String authorId, String activity, String& ExecutionError, SqlConnection& connectionObj, SqlCommand& 
 CommandObj) in Y:\Data\WebApp\Process\Feeds_DB.vb:line 63

Insert rows into a table is fast enough if only the table is not overloaded by indexes or gross mistake in DB struture. I think the problem should be sought in locks

Do not try to delete "million" rows at a time, it locks the table for a long period

DECLARE @top int = 1000;

WHILE 1=1
BEGIN
    DELETE TOP (@top)
    FROM MyTable
    WHERE dateEntered < @date;

    IF @@ROWCOUNT = 0 BREAK;
    WAITFOR DELAY '00:00:00.003'
END;