Timeout expired in SqlCommand.ExecuteNonQuery (insert) for SQL Server 2005

Hi,

I am upgrading from VS2000 & SQL Server 2000 to VS2005 & SQL Server 2005.

One of my massive programs has an exception after a lot of inserts to a certain

table during insert via SqlCommand.ExecuteNonQuery:

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

trace:
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(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

the SqlCommand.CommandTimeout is 600 !

this never happened when I ran the program on the 2000 versions.

please help !

Answer this question

Timeout expired in SqlCommand.ExecuteNonQuery (insert) for SQL Server 2005

vkuttyp

Hi all,

Please ensure that you close your connection right away after you made an access to your database. This is specially happend when you call several transactions you do with your data. Juz really make sure to close your connections after query or transaction.

Hth,

Michael Castillones, C# MCAD
Mr pinchy

I've found that this happens with my programs as well. Usually on the first attempt to send a command on a new connection.

What I do is put the code that sends such a command into a try...catch block and resend on a commandtimeout exception up to three times (after the third time, I assume there's an error with the network that's causing the problem and throw the exception to be handled higher up in my code.

Usually, I never get a third timeout. The commandtimeout exception is thrown on a non-responsive connection as well as on an actual timeout. What makes SQL 2005 non responsive more often than 2000 behooves me, but it does and that is my workaround in my code.

Hope this helps.
Gary 1981

Hi there,

Try using stored procedure to speed up the performance of your code. Coded sql statement means an "ad hoc query" command.

This is good explanation for the difference of "ad hoc query" and stored procedure:

An ad hoc query consists of SQL statements that are sent to the server. A stored procedure consists of SQL statements that are permanently stored on the server.

Hth,
Michael Castillones
asureus
well it didn't help.

I only managed to improve to 958,138 rows, but the job needs more inserts and I got the same exception. I forgot to mention that I am using the STANDARD edition of SQL Server 2005 so I can't use partitioning.
silkkeng
before the problematic job step, the table has 931,806 rows.

it always throws the exception after there are 935,444 rows (when I try to insert row # 935,445).

the table has identity field as primary index and one more unique nonclustered index on 7 columns of datetime, smallint & int types.

I moved the table data to a new file group in SS 2005 (.ndf) and the index to a second new file group and shrank the database. (before that the table was on PRIMARY).

will see if that helps
Lino
I tried to use Connection LifeTime=50 but it didn't help
nono92_b
I am using SqlCommand (coded - not a SP)
Upsetian

hi,

How many inserts did you do If you have a lot of inserts, use transaction to ensure that there are no problems encountered when the insert executes. our having a large data to query, make use of a batch query so that time out period for your query will be shortened.

hth,

Michael Castillones
Per S

Hi

i think your propelm is

1- transaction dead lock

or

2-your database is too big and your processore is slow

you have to increase the time out of your connection but anyway may be not work

so you have to split your database or use big server or make index on your database

if that work please tell me

i hope it is helpfull
holthaus
How many inserts are you doing
Are there a lot of indexes on that table
Is there a clustered index on that table, on a column that changes often or who's value does not gradually increment (For instance, a clustered index on a GUID field )
Sodan

I am also running into a timeout problem occasionally - If it happens, it will always happen on the first sql command on the first run of the application. It must have something to do with the time it takes to start an instance of Sql Server.

I tried to put something like

public override int CommandTimeout { get; set; }

and

CommandTimeout = 100;

But this gave a compile error.

I guess I will try someithing like the suggestion for 3 attempts in a try catch block

Any Ideas on changing this timeout

Thanks

I
Luke B
hi,

Ensure that your connection has been close. If your connection time out takes about 20 minutes this process takes about 20 minutes and the when it reach 20 minutes the pool is unavailable. Have this solution: add this to your connection string. ";Connection LifeTime=30". Timeout now is 30 seconds. If this still not working, make use of the indexing.

hth,
Michael Castillones
mluckham

hi,

Are you using stored procedures when executing the insert command or your doing the coded sql statement which makes the execution slower

Michael Castillones
Timeout expired in SqlCommand.ExecuteNonQuery (insert) for SQL Server 2005

Answer this question