sqlcommand.timeout

Question

sqlcommand.timeout

sqlfriend

SSC Guru

Points: 52781
More actions
April 25, 2022 at 7:13 pm

#4022019

I have a ssis package that gets data from oracle to SQLserver, use ado.net source task. Very occasionally I got a failure message that says ora-03113 end of file on communication channel. This usually means a server disconnected from oracle. a sudden drop of connection. I see in ado.net there is a property for sqlcommand.timeout, which is 30 seconds by default. I know also oracle server was running at that time, but there could be a snapshot backup running while the job run. I tried to understand what is the sqlcommand.timeout in MS doc:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlcommand.commandtimeout?view=sqlclient-dotnet-standard-4.1
========
Gets or sets the wait time (in seconds) before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
The time in seconds to wait for the command to execute. The default is 30 seconds.
==========
I have a hard time to understand the above two statements.
Does the 30 seconds mean it will wait for 30 seconds every time it runs to execute SQL command according to the second statement?
or it means if the connection dropped or failed, it will retry to connect in the 30 second interval?

THanks
August 2, 2023 at 2:19 am

This was removed by the editor as SPAM
August 2, 2023 at 9:55 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

it means that if whatever command you are executing (a update/delete query or a exec of a SP, if it takes more than 30 seconds it will terminate its execution and return to the code with a timeout error.

so for atomic small executions leaving the default is fine, for things that take time to execute setting it to a higher value is required to allow for completion of the sql statement being executed.

very common in etl jobs to set it to 0 (zero) so it will never timeout and will take whatever time is required to finish.

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

my total job takes 2 minutes if it is a successful run. so very quick. The ado.net source is a sql select query, it then goes to SQL server destination table.

ado.net source connects Oracle database and then do a select query, while it is doing so, there is a disruption that dropped the connection, if I make the timeout to a bigger number, will that help?

Thanks much

frederico_fonseca SSCoach Points: 16155 More actions · Answer 3

there are multiple things that can cause a connection to drop - increasing the timeout will not prevent those as it is only meant to prevent the timeout error itself.

for the particular error you got the underlying reason can vary a lot - you may have more info on the oracle logs. see http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm for some info on this error.