SqlCommand Timeout

Question

I have C#(2003) console program that inserts 150k new rows into a SQL 2000 table. The job runs daily at 2AM when the server is bascially idle. It fails with a SqlCommand Timeout approx once every 3 attempts. Restarting one or mulitple times will resolve the issue. The timeout is set to the default of 20 seconds. I have run PSSDIAG on SQL mulitple times and get consistent results. CPU Util is 20% or <, IO Read and Write rates are under 100 I/O second (Dell Server with Raid 5 SCSI Disk, 6 physical drives) and minimal page faulting is occuring (< .5 avg.) When looking at the SQL TRACE files I can see the timed out insert statement followed by the ATTENTION  EventClass created by the Sql Data Provider. When subtracting the start and end time stamps on the INSERT statement just prior to the ATTENTION I get an elapsed time of 0. The duration column is also 0, reads and writes total under 20. The starttime on the INSERT and the starttime on the ATTENTION are only a few milliseconds apart. It apprears to me that SQL server is working fine and the .NET Sql Client is generating the ATTENTION prematurely to signal the timeout. The ATTENTION always comes after the SQL statement completion. Has anybody else run into this or have a suggestion? I found a similar problem under http://support.microsoft.com/default.aspx?scid=kb;en-us;823679 but it involves timeout errors when setting the SqlComand.Timeout property to 0 which I'm not doing.

PS -- the console program is running on the same box as SQL.

Answer 1

Just an idea - Does your SQL table have a clustered index? That would affect performance when inserting that many rows, which might cause the timeout...

Answer 2

Clustered Index is identity column Decimal (18,0). Should be ok?

Answer 3

U R using Stored procedure or simple SQL Query  to execute the insert statement ?

if it is simple sql query than make sure the query does not contain execution plan so you need to increase you command.timout period.

I hope I could help you.