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:
> > 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.newsg...@.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.|||This is a multi-part message in MIME format.
--060303080103060806080308
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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?
--
Mike
http://sqlnerd.blogspot.com
tony.newsgrps@.gmail.com wrote:
> 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:
>> 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.newsg...@.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.
>
--060303080103060806080308
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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?</tt><br>
<pre class="moz-signature" cols="72">--
Mike
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></pre>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></pre>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tony.newsgrps@.gmail.com">tony.newsgrps@.gmail.com</a> wrote:
<blockquote
cite="mid:fe4cb261-5e49-4db9-9b19-5c67612ed748@.w40g2000hsb.googlegroups.com"
type="cite">
<pre wrap="">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
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:LinchiS...@.discussions.microsoft.com"><LinchiS...@.discussions.microsoft.com></a> wrote:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap="">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
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:tony.newsg...@.gmail.com">"tony.newsg...@.gmail.com"</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi newsgroup.
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">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();
}
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Any help or pointer greatly appreciated.
Tony.
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060303080103060806080308--|||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:
> 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:
> > 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:
> >> 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.newsg...@.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.|||This is a multi-part message in MIME format.
--090507060108060606060307
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Do you have many indexes on the tables you're modifying? If you have,
say, 20 indexes on a table and you insert 1 row in that table, SQL
Server will do a lot more I/Os than just what's needed for the insert in
the clustered index (or heap) because it has to maintain all the
nonclustered indexes.
Another possibility is your transaction is causing a lot of page splits
(which will also cause more modifications than you are expecting because
it's not just changing the page the row is inserted into, it would be
doing extra index page allocations, updating IAM pages, etc.). Can you
check the logical fragmentation of the relevant indexes? If they're
fragmenting quickly this could be the reason. (It also depends on the
values of the index keys you're inserting, as to whether they get
inserted in the middle of the index (possibly causing splits) or towards
the end of the index.)
These are just a couple of a number of reasons that your transaction log
might be growing more rapidly than you expect. Where I was going with
the autogrow settings on the log file was that if it was set to grow by
a large amount (eg. 1GB or 50% or something like that) then when it
needed to grow from its initial small size, of say 200MB, then the
autogrow interval might have caused it to exceed 1GB in a single grow.
But if you have it set at 1GB (and you don't want it to grow any
larger), why don't you turn off autogrow and set initial size of the log
at a hard 1GB? (Not that that's particularly important - just a bit
better than autogrowing files during transactions.)
If you're really keen to know the answer to your dilemma you could look
through the log to see what it's putting in there. There is no official
Microsoft way of doing this. In SQL 2000 there was an undocumented
function called ::fn_dblog() that you could use to look in the current
transaction log (not log backups) but the interpreting the output is a
bit difficult (as it's not documented and not very straight forward).
You query it like this:
select * from ::fn_dblog(null, null);
I don't know, off the top of my head, if this function is still in SQL
2005 (I suspect not) or if there are official replacements for it in the
DMVs (I suspect not again). A better way would be to buy one of the 3rd
party tools on the market (Lumigent Log Explorer springs to mind) that
do pretty much the same thing (and more) except in a more user-friendly
interface.
--
Mike
http://sqlnerd.blogspot.com
tony.newsgrps@.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:
>> 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:
>> 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:
>> 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.newsg...@.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.
>
--090507060108060606060307
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Do you have many indexes on the tables you're modifying? If you
have, say, 20 indexes on a table and you insert 1 row in that table,
SQL Server will do a lot more I/Os than just what's needed for the
insert in the clustered index (or heap) because it has to maintain all
the nonclustered indexes.<br>
<br>
Another possibility is your transaction is causing a lot of page splits
(which will also cause more modifications than you are expecting
because it's not just changing the page the row is inserted into, it
would be doing extra index page allocations, updating IAM pages,
etc.). Can you check the logical fragmentation of the relevant
indexes? If they're fragmenting quickly this could be the reason. (It
also depends on the values of the index keys you're inserting, as to
whether they get inserted in the middle of the index (possibly causing
splits) or towards the end of the index.)<br>
<br>
These are just a couple of a number of reasons that your transaction
log might be growing more rapidly than you expect. Where I was going
with the autogrow settings on the log file was that if it was set to
grow by a large amount (eg. 1GB or 50% or something like that) then
when it needed to grow from its initial small size, of say 200MB, then
the autogrow interval might have caused it to exceed 1GB in a single
grow. But if you have it set at 1GB (and you don't want it to grow any
larger), why don't you turn off autogrow and set initial size of the
log at a hard 1GB? (Not that that's particularly important - just a
bit better than autogrowing files during transactions.)<br>
<br>
If you're really keen to know the answer to your dilemma you could look
through the log to see what it's putting in there. There is no
official Microsoft way of doing this. In SQL 2000 there was an
undocumented function called ::fn_dblog() that you could use to look in
the current transaction log (not log backups) but the interpreting the
output is a bit difficult (as it's not documented and not very straight
forward). You query it like this:<br>
<br>
select * from ::fn_dblog(null, null);<br>
<br>
I don't know, off the top of my head, if this function is still in SQL
2005 (I suspect not) or if there are official replacements for it in
the DMVs (I suspect not again). A better way would be to buy one of
the 3rd party tools on the market (Lumigent Log Explorer springs to
mind) that do pretty much the same thing (and more) except in a more
user-friendly interface.</tt><br>
<pre class="moz-signature" cols="72">--
Mike
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></pre>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></pre>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tony.newsgrps@.gmail.com">tony.newsgrps@.gmail.com</a> wrote:
<blockquote
cite="mid:7329d471-f68e-423a-820b-59fa77390c2d@.i29g2000prf.googlegroups.com"
type="cite">
<pre wrap="">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 <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:e1mins...@.gmail.com"><e1mins...@.gmail.com></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tony.newsg...@.gmail.com">tony.newsg...@.gmail.com</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi Linchi,
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">Thank you,
Tony.
</pre>
</blockquote>
<blockquote type="cite">
<pre wrap="">On Dec 11, 10:44 pm, Linchi Shea
<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:LinchiS...@.discussions.microsoft.com"><LinchiS...@.discussions.microsoft.com></a> wrote:
</pre>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Linchi
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap=""><a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:tony.newsg...@.gmail.com">"tony.newsg...@.gmail.com"</a> wrote:
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Hi newsgroup.
</pre>
</blockquote>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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();
}
</pre>
</blockquote>
</blockquote>
</blockquote>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Any help or pointer greatly appreciated.
Tony.
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--090507060108060606060307--