ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.

ADO.net or TSQL Transactions

Hi all
Should implement a transaction in both the stored procedure AND in ADO.net
code or is doing it in one or the other good enough to protect against
concurrency and atomicity problems?
Thanks
Simon
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.
4ax.com...
> If you have complex explicit transactions, your best bet is going to
> be to implement them in your stored procedure(s) in T-SQL both in
> terms of performance and simplicity. Implementing them in client code
> may cause more round trips and not be as performant. Call a single
> stored procedure that executes the transactions and returns
> success/failure information in output parameters. See the BEGIN
> TRANSACTION and related topics in SQL Books Online for more
> information.
> --Mary
> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> <sh856531@.microsofts_free_email_service.com> wrote:
>
>Simon Harvey wrote:[vbcol=seagreen]
> Hi all
> Should implement a transaction in both the stored procedure AND in
> ADO.net code or is doing it in one or the other good enough to
> protect against concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.
4ax.com...
If you're talking about SQL running from a client as opposed to running
a single stored procedure, I think you have to go the SP route (in most
cases). Security aside, imagine running 20 SQL commands from a client on
the server in succession: each one requires a full round-trip to the
server which can slow things down. OTOH, a single SP is a single call.
While the slower client-side transaction runs, it holds locks on the
server, which in turn causes other transactions to wait on locked
resources which slows everyone down.
Plus, if you have to make an implementation change, you don't have to
deal with recompiling the app and distributing it to everyone.
David Gugick
Imceda Software
www.imceda.com|||A transaction has to be ATOMIC regardless of who starts it otherwise what
good is it. So two transactions are not better than one in this case. If
the outer one is Rolled back then ALL the nested ones are too. Sometimes it
makes sense to have trans in sp's so that if you call the sp by itself
everything inside it is all or nothing. But if you begin a tran from
outside a sp, everything from there on will be wrapped in that same tran.
Andrew J. Kelly SQL MVP
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:eOl8GlkzEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Should implement a transaction in both the stored procedure AND in ADO.net
> code or is doing it in one or the other good enough to protect against
> concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.
4ax.com...
>|||Except that sometimes you need to do something and then do more things in
source code that depend on that something and so on. Unless you move your
entire application into the SP, you'll have no choice but to wrap the
transaction into an ADO.NET call. Apart from the fact that (in our case) it
may not be feasible to rewrite 2.5 million lines of code to accomodate SP
only transactions, not to mention that some of the older parts of the code
(that are being rewritten into .NET) use embedded SQL...
You'll just have to evaluate your application architecture if it already
exists, or decide how you will be accessing the data at all times in your
application of you are currently designing it. There's a time and place for
everything. But know this, if you begin a transaction in the SP and for
some reason need to execute source code that may depending on something from
a currently running transaction, and it attempts to start a transaction and
call an SP that starts its own transaction, you'll get an exception. So you
either do it one way or another but not both, unless you want to excercise
your patients and stamina.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ut0iiRlzEHA.824@.TK2MSFTNGP11.phx.gbl...
> Simon Harvey wrote:
> If you're talking about SQL running from a client as opposed to running
> a single stored procedure, I think you have to go the SP route (in most
> cases). Security aside, imagine running 20 SQL commands from a client on
> the server in succession: each one requires a full round-trip to the
> server which can slow things down. OTOH, a single SP is a single call.
> While the slower client-side transaction runs, it holds locks on the
> server, which in turn causes other transactions to wait on locked
> resources which slows everyone down.
> Plus, if you have to make an implementation change, you don't have to
> deal with recompiling the app and distributing it to everyone.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> Except that sometimes you need to do something and then do more
> things in source code that depend on that something and so on.
> Unless you move your entire application into the SP, you'll have no
> choice but to wrap the transaction into an ADO.NET call. Apart from
> the fact that (in our case) it may not be feasible to rewrite 2.5
> million lines of code to accomodate SP only transactions, not to
> mention that some of the older parts of the code (that are being
> rewritten into .NET) use embedded SQL...
> You'll just have to evaluate your application architecture if it
> already exists, or decide how you will be accessing the data at all
> times in your application of you are currently designing it. There's
> a time and place for everything. But know this, if you begin a
> transaction in the SP and for some reason need to execute source code
> that may depending on something from a currently running transaction,
> and it attempts to start a transaction and call an SP that starts its
> own transaction, you'll get an exception. So you either do it one
> way or another but not both, unless you want to excercise your
> patients and stamina.
>
> Thanks,
> Shawn
I thought the OP asked a simple question: Which is better to use in an
application SPs or embedeed SQL. The answer to _that_ simple question is
stored procedures. But that's not the question he asked. He asked
whether the transaction should be started in the app or on in the SP. I
think Andrew answered that question. But I stand by answer to a question
that was never asked :-)
David Gugick
Imceda Software
www.imceda.com|||I think the relevance, while not as direct as previous answers, is that "it
depends on how your code and workflow is organized". I'm not dissagreeing
with the "simple" answer, that you use transactions in the SP. However,
I've rarely encountered a business application that was designed in such a
way that all transactions were at the SP level. I was only offering a
difference perspective, a difference way of looking at things, another thing
to take into consideration. Nothing more.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u4zdwPmzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Shawn B. wrote:
>
> I thought the OP asked a simple question: Which is better to use in an
> application SPs or embedeed SQL. The answer to _that_ simple question is
> stored procedures. But that's not the question he asked. He asked
> whether the transaction should be started in the app or on in the SP. I
> think Andrew answered that question. But I stand by answer to a question
> that was never asked :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> I think the relevance, while not as direct as previous answers, is
> that "it depends on how your code and workflow is organized". I'm
> not dissagreeing with the "simple" answer, that you use transactions
> in the SP. However, I've rarely encountered a business application
> that was designed in such a way that all transactions were at the SP
> level. I was only offering a difference perspective, a difference
> way of looking at things, another thing to take into consideration.
> Nothing more.
>
> Thanks,
> Shawn
>
I agree with you Shawn. Unless you have strict standards (which are not
bad to have), most applications will have some embedded SQL, even if
using SPs is the standard. Although, I'm not sure if you have an SP
standard for an app that ending up with a mixed code base is good from a
maintenance and security standpoint. It's nice to know no one can access
your database, except through stored procs.
David Gugick
Imceda Software
www.imceda.com|||Hi all,
Thanks for your answers so far.
I'm still not sure about something. Are ADO.net transactions and TSQL
transactions essentially equivelent?
If I do a big stored procedure, locked through ADO.net, will all the the
rows be locked until the SP returns?
I'm worried that if I use ADO, the stored procedure still might cause a
concurrency problem. This is essentially my problem.
Thanks again
Simon|||Simon,
An ADO transaction (.net or otherwise) is nothing more than passing a BEGIN
TRAN to SQL Server. So it's always a SQL Server transaction and any rows
locked after the first Begin Tran is issued (regardless of where or by who)
on that connection will remain locked until the final commit (if nested) or
the first Rollback.
Andrew J. Kelly SQL MVP
"Simon" <sh856531@.microsofts_free_email_service.com> wrote in message
news:%23GAkzXyzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Thanks for your answers so far.
> I'm still not sure about something. Are ADO.net transactions and TSQL
> transactions essentially equivelent?
> If I do a big stored procedure, locked through ADO.net, will all the the
> rows be locked until the SP returns?
> I'm worried that if I use ADO, the stored procedure still might cause a
> concurrency problem. This is essentially my problem.
> Thanks again
> Simon
>|||Andrew J. Kelly wrote:
> Simon,
> An ADO transaction (.net or otherwise) is nothing more than passing a
> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
> and any rows locked after the first Begin Tran is issued (regardless
> of where or by who) on that connection will remain locked until the
> final commit (if nested) or the first Rollback.
>
I would add that if concurrency is a concern, letting SQL Server start
and end the transaction from within the SP will be faster than doing so
from .net because it will eliminate 2 additional round-trips to the
server.
David Gugick
Imceda Software
www.imceda.com

ADO.net or TSQL Transactions

Hi all
Should implement a transaction in both the stored procedure AND in ADO.net
code or is doing it in one or the other good enough to protect against
concurrency and atomicity problems?
Thanks
Simon
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> If you have complex explicit transactions, your best bet is going to
> be to implement them in your stored procedure(s) in T-SQL both in
> terms of performance and simplicity. Implementing them in client code
> may cause more round trips and not be as performant. Call a single
> stored procedure that executes the transactions and returns
> success/failure information in output parameters. See the BEGIN
> TRANSACTION and related topics in SQL Books Online for more
> information.
> --Mary
> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> <sh856531@.microsofts_free_email_service.com> wrote:
>
Simon Harvey wrote:[vbcol=seagreen]
> Hi all
> Should implement a transaction in both the stored procedure AND in
> ADO.net code or is doing it in one or the other good enough to
> protect against concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
If you're talking about SQL running from a client as opposed to running
a single stored procedure, I think you have to go the SP route (in most
cases). Security aside, imagine running 20 SQL commands from a client on
the server in succession: each one requires a full round-trip to the
server which can slow things down. OTOH, a single SP is a single call.
While the slower client-side transaction runs, it holds locks on the
server, which in turn causes other transactions to wait on locked
resources which slows everyone down.
Plus, if you have to make an implementation change, you don't have to
deal with recompiling the app and distributing it to everyone.
David Gugick
Imceda Software
www.imceda.com
|||A transaction has to be ATOMIC regardless of who starts it otherwise what
good is it. So two transactions are not better than one in this case. If
the outer one is Rolled back then ALL the nested ones are too. Sometimes it
makes sense to have trans in sp's so that if you call the sp by itself
everything inside it is all or nothing. But if you begin a tran from
outside a sp, everything from there on will be wrapped in that same tran.
Andrew J. Kelly SQL MVP
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:eOl8GlkzEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Should implement a transaction in both the stored procedure AND in ADO.net
> code or is doing it in one or the other good enough to protect against
> concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>
|||Except that sometimes you need to do something and then do more things in
source code that depend on that something and so on. Unless you move your
entire application into the SP, you'll have no choice but to wrap the
transaction into an ADO.NET call. Apart from the fact that (in our case) it
may not be feasible to rewrite 2.5 million lines of code to accomodate SP
only transactions, not to mention that some of the older parts of the code
(that are being rewritten into .NET) use embedded SQL...
You'll just have to evaluate your application architecture if it already
exists, or decide how you will be accessing the data at all times in your
application of you are currently designing it. There's a time and place for
everything. But know this, if you begin a transaction in the SP and for
some reason need to execute source code that may depending on something from
a currently running transaction, and it attempts to start a transaction and
call an SP that starts its own transaction, you'll get an exception. So you
either do it one way or another but not both, unless you want to excercise
your patients and stamina.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ut0iiRlzEHA.824@.TK2MSFTNGP11.phx.gbl...
> Simon Harvey wrote:
> If you're talking about SQL running from a client as opposed to running
> a single stored procedure, I think you have to go the SP route (in most
> cases). Security aside, imagine running 20 SQL commands from a client on
> the server in succession: each one requires a full round-trip to the
> server which can slow things down. OTOH, a single SP is a single call.
> While the slower client-side transaction runs, it holds locks on the
> server, which in turn causes other transactions to wait on locked
> resources which slows everyone down.
> Plus, if you have to make an implementation change, you don't have to
> deal with recompiling the app and distributing it to everyone.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Shawn B. wrote:
> Except that sometimes you need to do something and then do more
> things in source code that depend on that something and so on.
> Unless you move your entire application into the SP, you'll have no
> choice but to wrap the transaction into an ADO.NET call. Apart from
> the fact that (in our case) it may not be feasible to rewrite 2.5
> million lines of code to accomodate SP only transactions, not to
> mention that some of the older parts of the code (that are being
> rewritten into .NET) use embedded SQL...
> You'll just have to evaluate your application architecture if it
> already exists, or decide how you will be accessing the data at all
> times in your application of you are currently designing it. There's
> a time and place for everything. But know this, if you begin a
> transaction in the SP and for some reason need to execute source code
> that may depending on something from a currently running transaction,
> and it attempts to start a transaction and call an SP that starts its
> own transaction, you'll get an exception. So you either do it one
> way or another but not both, unless you want to excercise your
> patients and stamina.
>
> Thanks,
> Shawn
I thought the OP asked a simple question: Which is better to use in an
application SPs or embedeed SQL. The answer to _that_ simple question is
stored procedures. But that's not the question he asked. He asked
whether the transaction should be started in the app or on in the SP. I
think Andrew answered that question. But I stand by answer to a question
that was never asked :-)
David Gugick
Imceda Software
www.imceda.com
|||I think the relevance, while not as direct as previous answers, is that "it
depends on how your code and workflow is organized". I'm not dissagreeing
with the "simple" answer, that you use transactions in the SP. However,
I've rarely encountered a business application that was designed in such a
way that all transactions were at the SP level. I was only offering a
difference perspective, a difference way of looking at things, another thing
to take into consideration. Nothing more.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u4zdwPmzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Shawn B. wrote:
>
> I thought the OP asked a simple question: Which is better to use in an
> application SPs or embedeed SQL. The answer to _that_ simple question is
> stored procedures. But that's not the question he asked. He asked
> whether the transaction should be started in the app or on in the SP. I
> think Andrew answered that question. But I stand by answer to a question
> that was never asked :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Shawn B. wrote:
> I think the relevance, while not as direct as previous answers, is
> that "it depends on how your code and workflow is organized". I'm
> not dissagreeing with the "simple" answer, that you use transactions
> in the SP. However, I've rarely encountered a business application
> that was designed in such a way that all transactions were at the SP
> level. I was only offering a difference perspective, a difference
> way of looking at things, another thing to take into consideration.
> Nothing more.
>
> Thanks,
> Shawn
>
I agree with you Shawn. Unless you have strict standards (which are not
bad to have), most applications will have some embedded SQL, even if
using SPs is the standard. Although, I'm not sure if you have an SP
standard for an app that ending up with a mixed code base is good from a
maintenance and security standpoint. It's nice to know no one can access
your database, except through stored procs.
David Gugick
Imceda Software
www.imceda.com
|||Hi all,
Thanks for your answers so far.
I'm still not sure about something. Are ADO.net transactions and TSQL
transactions essentially equivelent?
If I do a big stored procedure, locked through ADO.net, will all the the
rows be locked until the SP returns?
I'm worried that if I use ADO, the stored procedure still might cause a
concurrency problem. This is essentially my problem.
Thanks again
Simon
|||Simon,
An ADO transaction (.net or otherwise) is nothing more than passing a BEGIN
TRAN to SQL Server. So it's always a SQL Server transaction and any rows
locked after the first Begin Tran is issued (regardless of where or by who)
on that connection will remain locked until the final commit (if nested) or
the first Rollback.
Andrew J. Kelly SQL MVP
"Simon" <sh856531@.microsofts_free_email_service.com> wrote in message
news:%23GAkzXyzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Thanks for your answers so far.
> I'm still not sure about something. Are ADO.net transactions and TSQL
> transactions essentially equivelent?
> If I do a big stored procedure, locked through ADO.net, will all the the
> rows be locked until the SP returns?
> I'm worried that if I use ADO, the stored procedure still might cause a
> concurrency problem. This is essentially my problem.
> Thanks again
> Simon
>
|||Andrew J. Kelly wrote:
> Simon,
> An ADO transaction (.net or otherwise) is nothing more than passing a
> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
> and any rows locked after the first Begin Tran is issued (regardless
> of where or by who) on that connection will remain locked until the
> final commit (if nested) or the first Rollback.
>
I would add that if concurrency is a concern, letting SQL Server start
and end the transaction from within the SP will be faster than doing so
from .net because it will eliminate 2 additional round-trips to the
server.
David Gugick
Imceda Software
www.imceda.com

ADO.net or TSQL Transactions

Hi all
Should implement a transaction in both the stored procedure AND in ADO.net
code or is doing it in one or the other good enough to protect against
concurrency and atomicity problems?
Thanks
Simon
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> If you have complex explicit transactions, your best bet is going to
> be to implement them in your stored procedure(s) in T-SQL both in
> terms of performance and simplicity. Implementing them in client code
> may cause more round trips and not be as performant. Call a single
> stored procedure that executes the transactions and returns
> success/failure information in output parameters. See the BEGIN
> TRANSACTION and related topics in SQL Books Online for more
> information.
>
> --Mary
>
> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> <sh856531@.microsofts_free_email_service.com> wrote:
>
>>Hi everyone,
>>
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>
>>Thanks
>>
>>Simon
>>
>Simon Harvey wrote:
> Hi all
> Should implement a transaction in both the stored procedure AND in
> ADO.net code or is doing it in one or the other good enough to
> protect against concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>> Hi everyone,
>> Can anyone tell me, if I use and ADO transaction object to execute
>> say 10 stored procedures, and the stored procedures are themselve
>> quite quite long
>> and multistaged, do I need to use transaction statements inside the
>> individual procedures to avoid potential concurrency issues, or am I
>> protected from this by virtue of the ADO transaction object.
>> The reason I ask is, it could be the case that the ADO.net
>> transaction simply ensures that the stored procedures operate in an
>> all or nothing manner. This may mean that within a complicated
>> multi staged stored procedure information could become corrupted
>> because the relevent multi staged code *inside* the procedure isn't
>> transacted. I hope that make sense. My query pertains to SQL Server
>> but I'm
>> guessing the
>> same would be true of any db that supports transactions and SProcs.
>> Thanks
>> Simon
If you're talking about SQL running from a client as opposed to running
a single stored procedure, I think you have to go the SP route (in most
cases). Security aside, imagine running 20 SQL commands from a client on
the server in succession: each one requires a full round-trip to the
server which can slow things down. OTOH, a single SP is a single call.
While the slower client-side transaction runs, it holds locks on the
server, which in turn causes other transactions to wait on locked
resources which slows everyone down.
Plus, if you have to make an implementation change, you don't have to
deal with recompiling the app and distributing it to everyone.
--
David Gugick
Imceda Software
www.imceda.com|||A transaction has to be ATOMIC regardless of who starts it otherwise what
good is it. So two transactions are not better than one in this case. If
the outer one is Rolled back then ALL the nested ones are too. Sometimes it
makes sense to have trans in sp's so that if you call the sp by itself
everything inside it is all or nothing. But if you begin a tran from
outside a sp, everything from there on will be wrapped in that same tran.
Andrew J. Kelly SQL MVP
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:eOl8GlkzEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Should implement a transaction in both the stored procedure AND in ADO.net
> code or is doing it in one or the other good enough to protect against
> concurrency and atomicity problems?
> Thanks
> Simon
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>>Hi everyone,
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>Thanks
>>Simon
>>
>|||Except that sometimes you need to do something and then do more things in
source code that depend on that something and so on. Unless you move your
entire application into the SP, you'll have no choice but to wrap the
transaction into an ADO.NET call. Apart from the fact that (in our case) it
may not be feasible to rewrite 2.5 million lines of code to accomodate SP
only transactions, not to mention that some of the older parts of the code
(that are being rewritten into .NET) use embedded SQL...
You'll just have to evaluate your application architecture if it already
exists, or decide how you will be accessing the data at all times in your
application of you are currently designing it. There's a time and place for
everything. But know this, if you begin a transaction in the SP and for
some reason need to execute source code that may depending on something from
a currently running transaction, and it attempts to start a transaction and
call an SP that starts its own transaction, you'll get an exception. So you
either do it one way or another but not both, unless you want to excercise
your patients and stamina.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ut0iiRlzEHA.824@.TK2MSFTNGP11.phx.gbl...
> Simon Harvey wrote:
> > Hi all
> >
> > Should implement a transaction in both the stored procedure AND in
> > ADO.net code or is doing it in one or the other good enough to
> > protect against concurrency and atomicity problems?
> >
> > Thanks
> >
> > Simon
> >
> > "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> > news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> >> If you have complex explicit transactions, your best bet is going to
> >> be to implement them in your stored procedure(s) in T-SQL both in
> >> terms of performance and simplicity. Implementing them in client code
> >> may cause more round trips and not be as performant. Call a single
> >> stored procedure that executes the transactions and returns
> >> success/failure information in output parameters. See the BEGIN
> >> TRANSACTION and related topics in SQL Books Online for more
> >> information.
> >>
> >> --Mary
> >>
> >> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> >> <sh856531@.microsofts_free_email_service.com> wrote:
> >>
> >> Hi everyone,
> >>
> >> Can anyone tell me, if I use and ADO transaction object to execute
> >> say 10 stored procedures, and the stored procedures are themselve
> >> quite quite long
> >> and multistaged, do I need to use transaction statements inside the
> >> individual procedures to avoid potential concurrency issues, or am I
> >> protected from this by virtue of the ADO transaction object.
> >>
> >> The reason I ask is, it could be the case that the ADO.net
> >> transaction simply ensures that the stored procedures operate in an
> >> all or nothing manner. This may mean that within a complicated
> >> multi staged stored procedure information could become corrupted
> >> because the relevent multi staged code *inside* the procedure isn't
> >> transacted. I hope that make sense. My query pertains to SQL Server
> >> but I'm
> >> guessing the
> >> same would be true of any db that supports transactions and SProcs.
> >>
> >> Thanks
> >>
> >> Simon
> If you're talking about SQL running from a client as opposed to running
> a single stored procedure, I think you have to go the SP route (in most
> cases). Security aside, imagine running 20 SQL commands from a client on
> the server in succession: each one requires a full round-trip to the
> server which can slow things down. OTOH, a single SP is a single call.
> While the slower client-side transaction runs, it holds locks on the
> server, which in turn causes other transactions to wait on locked
> resources which slows everyone down.
> Plus, if you have to make an implementation change, you don't have to
> deal with recompiling the app and distributing it to everyone.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> Except that sometimes you need to do something and then do more
> things in source code that depend on that something and so on.
> Unless you move your entire application into the SP, you'll have no
> choice but to wrap the transaction into an ADO.NET call. Apart from
> the fact that (in our case) it may not be feasible to rewrite 2.5
> million lines of code to accomodate SP only transactions, not to
> mention that some of the older parts of the code (that are being
> rewritten into .NET) use embedded SQL...
> You'll just have to evaluate your application architecture if it
> already exists, or decide how you will be accessing the data at all
> times in your application of you are currently designing it. There's
> a time and place for everything. But know this, if you begin a
> transaction in the SP and for some reason need to execute source code
> that may depending on something from a currently running transaction,
> and it attempts to start a transaction and call an SP that starts its
> own transaction, you'll get an exception. So you either do it one
> way or another but not both, unless you want to excercise your
> patients and stamina.
>
> Thanks,
> Shawn
I thought the OP asked a simple question: Which is better to use in an
application SPs or embedeed SQL. The answer to _that_ simple question is
stored procedures. But that's not the question he asked. He asked
whether the transaction should be started in the app or on in the SP. I
think Andrew answered that question. But I stand by answer to a question
that was never asked :-)
David Gugick
Imceda Software
www.imceda.com|||I think the relevance, while not as direct as previous answers, is that "it
depends on how your code and workflow is organized". I'm not dissagreeing
with the "simple" answer, that you use transactions in the SP. However,
I've rarely encountered a business application that was designed in such a
way that all transactions were at the SP level. I was only offering a
difference perspective, a difference way of looking at things, another thing
to take into consideration. Nothing more.
Thanks,
Shawn
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u4zdwPmzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Shawn B. wrote:
> > Except that sometimes you need to do something and then do more
> > things in source code that depend on that something and so on.
> > Unless you move your entire application into the SP, you'll have no
> > choice but to wrap the transaction into an ADO.NET call. Apart from
> > the fact that (in our case) it may not be feasible to rewrite 2.5
> > million lines of code to accomodate SP only transactions, not to
> > mention that some of the older parts of the code (that are being
> > rewritten into .NET) use embedded SQL...
> >
> > You'll just have to evaluate your application architecture if it
> > already exists, or decide how you will be accessing the data at all
> > times in your application of you are currently designing it. There's
> > a time and place for everything. But know this, if you begin a
> > transaction in the SP and for some reason need to execute source code
> > that may depending on something from a currently running transaction,
> > and it attempts to start a transaction and call an SP that starts its
> > own transaction, you'll get an exception. So you either do it one
> > way or another but not both, unless you want to excercise your
> > patients and stamina.
> >
> >
> > Thanks,
> > Shawn
>
> I thought the OP asked a simple question: Which is better to use in an
> application SPs or embedeed SQL. The answer to _that_ simple question is
> stored procedures. But that's not the question he asked. He asked
> whether the transaction should be started in the app or on in the SP. I
> think Andrew answered that question. But I stand by answer to a question
> that was never asked :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> I think the relevance, while not as direct as previous answers, is
> that "it depends on how your code and workflow is organized". I'm
> not dissagreeing with the "simple" answer, that you use transactions
> in the SP. However, I've rarely encountered a business application
> that was designed in such a way that all transactions were at the SP
> level. I was only offering a difference perspective, a difference
> way of looking at things, another thing to take into consideration.
> Nothing more.
>
> Thanks,
> Shawn
>
I agree with you Shawn. Unless you have strict standards (which are not
bad to have), most applications will have some embedded SQL, even if
using SPs is the standard. Although, I'm not sure if you have an SP
standard for an app that ending up with a mixed code base is good from a
maintenance and security standpoint. It's nice to know no one can access
your database, except through stored procs.
--
David Gugick
Imceda Software
www.imceda.com|||Hi all,
Thanks for your answers so far.
I'm still not sure about something. Are ADO.net transactions and TSQL
transactions essentially equivelent?
If I do a big stored procedure, locked through ADO.net, will all the the
rows be locked until the SP returns?
I'm worried that if I use ADO, the stored procedure still might cause a
concurrency problem. This is essentially my problem.
Thanks again
Simon|||Simon,
An ADO transaction (.net or otherwise) is nothing more than passing a BEGIN
TRAN to SQL Server. So it's always a SQL Server transaction and any rows
locked after the first Begin Tran is issued (regardless of where or by who)
on that connection will remain locked until the final commit (if nested) or
the first Rollback.
--
Andrew J. Kelly SQL MVP
"Simon" <sh856531@.microsofts_free_email_service.com> wrote in message
news:%23GAkzXyzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Thanks for your answers so far.
> I'm still not sure about something. Are ADO.net transactions and TSQL
> transactions essentially equivelent?
> If I do a big stored procedure, locked through ADO.net, will all the the
> rows be locked until the SP returns?
> I'm worried that if I use ADO, the stored procedure still might cause a
> concurrency problem. This is essentially my problem.
> Thanks again
> Simon
>|||Andrew J. Kelly wrote:
> Simon,
> An ADO transaction (.net or otherwise) is nothing more than passing a
> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
> and any rows locked after the first Begin Tran is issued (regardless
> of where or by who) on that connection will remain locked until the
> final commit (if nested) or the first Rollback.
>
I would add that if concurrency is a concern, letting SQL Server start
and end the transaction from within the SP will be faster than doing so
from .net because it will eliminate 2 additional round-trips to the
server.
--
David Gugick
Imceda Software
www.imceda.com|||I agree, you always want to keep the transactions as short as possable. I
was just trying to get the point across that there really is no such thing
as an ADO tran, it is really SQL Server that is managing it.
--
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23a4gWk0zEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
>> Simon,
>> An ADO transaction (.net or otherwise) is nothing more than passing a
>> BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
>> and any rows locked after the first Begin Tran is issued (regardless
>> of where or by who) on that connection will remain locked until the
>> final commit (if nested) or the first Rollback.
> I would add that if concurrency is a concern, letting SQL Server start and
> end the transaction from within the SP will be faster than doing so from
> .net because it will eliminate 2 additional round-trips to the server.
> --
> David Gugick
> Imceda Software
> www.imceda.com

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:
>
>
>
>
>
>

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