Timeout problem with large update query from VB.Net to SQL Server 2000

[Greg]

Hi all,

I am doing some large updates,
that may update 10,000 plus rows.

This works fine when I execute the SQL directly
in Query Analyzer.

If I set the timeout on my VB connection to 0 (zero)
the connection should not time out????

But it does.

If I set the time out to a high value, say 1200,
I get the same problem well within 1200 seconds.

Also, I am getting the problem that the log fills up,
but it is set to auto grow????

Any ideas would be appreciated.

Thanks
Greg

[Erland Sommarskog]

Greg (gr****@harrisconsultinggroup.com) writes:

I am doing some large updates,
that may update 10,000 plus rows.

This works fine when I execute the SQL directly
in Query Analyzer.

If I set the timeout on my VB connection to 0 (zero)
the connection should not time out????

But it does.
Which .Net Data Provider do you use? SqlClient, OleDB or ODBC? I have
also seen situations where SqlClient has not understood the meaning of 0,
but has timed out nevertheless.
If I set the time out to a high value, say 1200,
I get the same problem well within 1200 seconds.
Seems like you need to set the timeout as high as your command runs...
Also, I am getting the problem that the log fills up,
but it is set to auto grow????

Looks like you need to see your local hardware vendor to get some more disk!
Note that there can be plenty of free space, and you still get the message
that the log is full. For instance, if the log is already 2 GB, you have 10%
auto-grow and 150 MB free, the autogrow will fail.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp