ADO.Net connection timeout and transaction log

Hi newsgroup.
Can someone help me figure out what happens to the transaction log
file when you run an ADO.Net transaction that times out? I have
configure the database recovery model to SIMPLE so normally the
transaction log file should never grow large, yet in some occasions, I
found it growing to several Gigs.
If the connection times out in the C# code and is eventually closed,
is Sql Server notified of it and does it roll back the transaction
right away or does it keep the transaction waiting around, thus
filling up the transaction log? What is the default time out before
Sql Server notices that the client got disconnected and that the
transaction must be rolled back?
I have the following code running. Is it possible that it would make
the transaction log grow when exceptions occur
// m_Connection is a SqlConnection object.
DbTransaction trans = m_Connection.BeginTransaction();
try
{
// Some transaction uploading large files and
// updating several records in several tables
trans.Commit();
}
catch( SqlException sqlEx )
{
trans.Rollback();
if( sqlEx.Number == SqlTimeoutError )
{
throw new NeedRetryException( "SqlTimeoutException
is occured.", sqlEx );
}
else
{
throw;
}
}
catch
{
trans.Rollback();
throw;
}
finally
{
trans.Dispose();
m_Connection.Close();
}
Any help or pointer greatly appreciated.
Tony.
> I have configure the database recovery model to SIMPLE so normally the
> transaction log file should never grow large, yet in some occasions, I
> found it growing to several Gigs.
The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
of a transaction that matters. In other words, you can have your database in
the simple mode, but if you run a large single transaction, you can still
blow up your log file.
Linchi
"tony.newsgrps@.gmail.com" wrote:

> Hi newsgroup.
> Can someone help me figure out what happens to the transaction log
> file when you run an ADO.Net transaction that times out? I have
> configure the database recovery model to SIMPLE so normally the
> transaction log file should never grow large, yet in some occasions, I
> found it growing to several Gigs.
> If the connection times out in the C# code and is eventually closed,
> is Sql Server notified of it and does it roll back the transaction
> right away or does it keep the transaction waiting around, thus
> filling up the transaction log? What is the default time out before
> Sql Server notices that the client got disconnected and that the
> transaction must be rolled back?
> I have the following code running. Is it possible that it would make
> the transaction log grow when exceptions occur
> // m_Connection is a SqlConnection object.
> DbTransaction trans = m_Connection.BeginTransaction();
> try
> {
> // Some transaction uploading large files and
> // updating several records in several tables
> trans.Commit();
> }
> catch( SqlException sqlEx )
> {
> trans.Rollback();
> if( sqlEx.Number == SqlTimeoutError )
> {
> throw new NeedRetryException( "SqlTimeoutException
> is occured.", sqlEx );
> }
> else
> {
> throw;
> }
> }
> catch
> {
> trans.Rollback();
> throw;
> }
> finally
> {
> trans.Dispose();
> m_Connection.Close();
> }
> Any help or pointer greatly appreciated.
> Tony.
>
|||Hi Linchi,
Thank you for the answer. The transactions I'm running may be large at
times (i.e. inserting perhaps 50MB of data), but the transaction log
grows larger than 1GB. That's a 20 time larger so to me it does not
sound like 1GB is a normal size for the transaction log. I can expect
100MB to be, but 1GB seems way too high.
What else could be the reason for that log file growing?
Thank you,
Tony.
On Dec 11, 10:44 pm, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The SIMPLE mode doesn't mean you won't get a large tran log. It's the size
> of a transaction that matters. In other words, you can have your database in
> the simple mode, but if you run a large single transaction, you can still
> blow up your log file.
> Linchi
> "tony.newsg...@.gmail.com" wrote:
>
>
|||Hi Mike,
I capped the log file to 1GB with no autogrow so when I say that the
transaction log file is growing, I actually mean that I'm reaching
that limit of 1GB and any subsequent transaction is failing because of
that.
I believe 1GB should be plenty enough. The largest data I would insert
is probably around 50MB split in say 1500 rows, all in one
transaction. I can't imagine that doing that transaction would
generate 1GB of logs in the transaction log file, would it? All my
transactions are serialized, so I'm sure that I don't have 20 of these
large ones running at the same time.
Thank you,
Tony
On Dec 12, 1:08 am, Mike Hodgson <e1mins...@.gmail.com> wrote:[vbcol=seagreen]
> The log file will only grow when it is full, you are modifying more data
> in the database and autogrow is turned on for the transaction log.
> (Yes, this is regardless of whether you are using the SIMPLE recovery
> model or not. The SIMPLE recovery model just means the tlog is
> truncated from time to time based on a number of factors.) What are
> your autogrow settings on that logical file?
> --
> Mikehttp://sqlnerd.blogspot.com
> tony.newsg...@.gmail.com wrote:
>
>
>
>
>
>