Timeout expired on Insert

[Sandhya Kumar]

Hi,

We have a huge table having around 29579242 Rows.The table is huge and there
is no clustered index on the table. There are a couple of non-clustered
indexes.
We have started experiencing these errors. We increased the command timeout
to 120 (2 minutes) but the error isstill occuring on Insert. Data is being
inserted 1 row at a time and is not transactional. Any ideas?

Thanks
Sandhya
With the Message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.

Stack 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)

 

[Brian Lampe]

Hi,

We have a huge table having around 29579242 Rows.The table is huge and there
is no clustered index on the table. There are a couple of non-clustered
indexes.
We have started experiencing these errors. We increased the command timeout
to 120 (2 minutes) but the error isstill occuring on Insert. Data is being
inserted 1 row at a time and is not transactional. Any ideas?

Thanks
Sandhya
With the Message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.

Stack 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(TdsParserStateObje­ct
stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)

If you're using 2.0 or greater, try this:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(vs.80).aspx
I've had very good results with it.

If you're not up to 2.0 yet, another option is to use DTS. You can
create a package and call it in your code. I've Inserted several
million rows in seconds with this.

Brian

 

[Cor Ligthert[MVP]]

Sandhya,

Do you use code, most probably is it only updating the first row in an
endless loop.

You will not be the first one doing that.



Cor

 

[WenYuan Wang [MSFT]]

Hello Sandhya Kumar

According to your description, you received an exception that "Timeout
expired." when inserting a row into a huge table (which includes 29579242
rows). Please correct me if I misunderstood anything.

Have you tried to set the command timeout to "0" (which indications no
limit)? Will you get the timeout expired exception again? Base on my
experience, such timeout issue should be resolved after we increased the
command timeout (SqlCommand.CommandTimeout property).

If the issue still persists, would you please paste the code snippet about
how you execute SQLCommand in newsgroup? We need more detailed information.
I will follow up. We are glad to assist you. Thanks.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Sandhya Kumar,

This is Wen Yuan again. Have you tried my suggest so far? What if you set
the timeout to "0" (No Limit)?
Please feel free to let me know if you have any more concern or any further
issue? We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Cor Ligthert[MVP]]

Wen Yuan,

I would not give a reaction earlier however now you sustain, setting a
connection time out to 0 is in the documentation from Microsoft a solution
that should be avoided. Have a look at what I wrote the chance that the
problem is that is in my idea more then 80%.

Cor

 

[WenYuan Wang [MSFT]]

Hello Cor,
Thanks for your suggestion.

I agree with you, setting time out property to 0 is not the solution for
this issue. I just want to check if Sandhya still receives the same error
message after setting the timeout property to 0. I wonder why the issue
still persists, even though the command timeout has been increased to 2
minutes. This sounds strange. If the issue still persists after we set the
time out to No limit, there must be something we are missing.

Therefore, I replied again, and want to know what happens after Sandhya set
the time out to 0. Thanks.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Walter Wang [MSFT]]

Hi Sandhya,

This is Walter, community lead of MSDN Managed Newsgroup Support Team.

First, I want to thank you for using our support service as a MSDN
subscriber.

Regarding this post, many community members and my colleague have given
some general suggestions to troubleshoot the issue. However, given the
information so far, we could only give suggestions and wait for your
confirmation if they help or not. Therefore we will appreciate if you could
drop a note here to let us know if the issue is now solved. Thank you very
much for your collaboration.


Sincerely,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.