NESTED TRANSACTIONS!

In case of nested transactions, will the @.@.TRANCOUNT value be always 0
if the entire transaction is rolled back at the very end?
Thanks,
ArpanIf I understand the question correctly, you are wondering what the value of
@.@.TRANCOUNT will be when you issue a ROLLBACK TRANSACTION at some point in
the processing before a COMMIT. If this is the question, then @.@.TRANCOUNT's
value will be 0.
"Arpan" wrote:

> In case of nested transactions, will the @.@.TRANCOUNT value be always 0
> if the entire transaction is rolled back at the very end?
> Thanks,
> Arpan
>|||Thanks, Shahryar, for your response. I know that ROLLBACK at some point
of time before a COMMIT statement will set @.@.TRANCOUNT to 0 but will
@.@.TRANCOUNT's value ALWAYS be 0 at the END OF A TRANSACTION assuming
that the transaction isn't COMMITted at the end?
Thanks,
Regards,
Arpan

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?
I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>
sql

Nested transactions

I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:

BEGIN TRANSACTION;

if (!functioncall1()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

if (!functioncall2()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

COMMIT TRANSACTION ;

Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?This gets rather complicated to explain, but I'll give it a shot.

SQL transactions don't nest in the strict (relational algebra) sense of the word. When you "nest" SQL transactions, they form something more like a procedure call stack where the COMMIT behaves like a return and the ROLLBACK behaves something like throwing an execption. The first rollback to come along basically wipes you back to step 1, before the first BEGIN TRANSACTION occured.

There were reasons for this behavior, once upon a time. There is still a reasonable logical arguments for maintaining the behavior, even though it goes so badly against the mental model used by third generation programming tools (like VB, VC, C#, etc).

The simplest solution I see to your problem is to avoid nesting, and use a try/throw/catch model to allow the application side logic to match the database side.

-PatP|||I have tried using the nested transactions and I can get them to work in the SQL query analyzer, but when I try to do the same thing in the application, the rollback will undo the work done in the inner most transaction, but not to the outer transaction.|||Ah... If that is the case, please post the Transact-SQL for what you want to do. It will be much easier to help you translate the Transact-SQL to C than to guess how it is different from what you've posted. What you posted won't behave the way you want it to, because SQL transactions don't work that way.

-PatP|||When I call function 1, the function completes and the transaction is completed. Function 2 doesn't finish and a rollback occurs inside the function, but the changes that were done in function 1 never get undone.

The SQL that I can get to work in the query analyzer is:

BEGIN Transaction Test1
Begin Transaction Test2
Insert into TABLE2
Commit Transaction Test2

BEGIN Transaction Test3
Insert into TABLE3
Commit Transaction
Rollback Transaction

Here is the C++ code that I can't get to work:

void main ( )
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function1( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function2( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;

////////////////////////////////////////////////////////////////////////////////////
int Function1( )
{

sqlStatement.Format(_T("INSERT INTO table1( VARIABLE LIST) FROM TABLE1");

try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

if (nRowCount != 1)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

sqlStatement.Format(_T("DELETE FROM TABLE2 ");

try
{
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
e->Delete();
return 0;
}


// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;
}

////////////////////////////////////////////////////////////////////////////////////
int Function2( )
{
// Clear any previous errors
ResetError();

// Start a transaction
try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
}
catch (CException *e)
{
return 0;
}
CString sqlStatement;
sqlStatement.Format( _T("DELETE FROM TABLE2 ");

try
{
ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
e->Delete();
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
}
sqlStatement.Format( _T( "INSERT INTO TABLE3( )"),
TRY
{
ExecuteSQL(sqlStatement);
}
CATCH( CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
END_CATCH

// commit the changes
TRY
{
ExecuteSQL( _T("COMMIT TRANSACTION"));
}
CATCH( CException, e)
{
return 0;
}
END_CATCH

return 1;
}

Nested Transactions

Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?Use SAVE TRAN in you "inner transaction" instead of BEGIN TRAN. That gives y
ou a savepoint to roll
back to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uIP6f6FSGHA.4976@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer
transaction's statements executing?|||You can't. From SQL BOL:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback
to any other name (other than a valid savepoint name) generates an
error. None of the statements executed before the rollback are in fact
rolled back at the time this error occurs. The statements are rolled
back only when the outer transaction is rolled back."|||Hi rmg66
Rollback can only be used to roll back to the outermost begin tran, or to a
savepoint. Note that there really is not concept of nesting transactions in
SQL Server. Please read about transaction control in the Books Online.
begin tran One -- the label here is useless
insert into XX (data) values ('Tran1')
SAVE tran Two -- this does not start a new transaction, it only marks a spot
in the log that we can roll back to
insert into XX (data) values ('Tran2')
rollback tran Two -- roll back to named savepoint
insert into XX (data) values ('Tran1')
Commit tran One -- the label here is useless
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uIP6f6FSGHA.497
6@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?

Nested Transaction: how to commit the outer SP even if the inner ones fails.

Since some days I'm facing problems with the nested transactions and I read the they are not fully supported in sql server 2005 so I'd need an help from a more experienced SS programming.

My outer SP must live inside a transaction. It calles an another SP but IT MUST NOT ROLLBACK in case the INNER SP fails.

My nested SP, let's call it INNERSP is quite complex because it involves INSERT-UPDATE-DELETE, mathematic calculation and calls other SP as well.

Of course I want to make the INNERSP error-proof and I'd like to wrap it inside a try..catch structure.

If my INNERSP is:

BEGIN TRY

Insert ... --this insert cause an error that is trapped in the BEGIN CATCH

BEGIN TRANSACTION
COMMIT

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH

The Rollback statement roll back also the transaction of the caller, but for me it is not acceptable.

I was investigating on the SAVE TRANSACTION statement but if I use it the @.@.TRANCOUNT is not decremented and my OUTERSP ends with a @.@.TRANCOUNT that is not zero, but from what I know "Nothing is actually committed until @.@.TRANCOUNT = 0"

Any helps is more that welcome!!
Thank you

Marina B.

Try putting your begin transaction statement before the work that you want inside of the transaction.|||

Interesting problem. It appears that even if an error in the called procedure is caught, the calling procedure's transaction still knows about it somehow:

if object_id('x') is not null drop table x

go

create table x(x1 int)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values ('x')

end try

begin catch

print 'caught px'

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

exec px

print @.@.error

insert x values (2)

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

0

caught py

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

*/

|||

Not surprisingly, this behavior is my design. More importantly, this only applies to "fatal" errors. Per BOL:

"A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block."

This script is changed from the one above: the error that occurs in px is not a "fatal" error (the insert of "x" into an integer column is considered a syntax error and is fatal), and after it is caught, the py procedure does complete the transaction, resulting in a new row being inserted into table x:

set nocount on

go

if object_id('x') is not null drop table x

go

create table x(x1 int primary key)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values (1)

end try

begin catch

print 'caught px'

print error_message()

print xact_state()

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

insert x values (1)

exec px

print @.@.error

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

Violation of PRIMARY KEY constraint 'PK__x__177C9889'. Cannot insert duplicate key in object 'dbo.x'.

1

0

*/

|||

SQL Server does not support autonomous transactions. Only way is to write an extended SP/SQLCLR SP to make a loopback connection to the database and call the inner SP without enlisting in same transaction. This technique of course has lot of disadvantages and side-effects. For one, you could create a distributed deadlock that is undetectable by SQL Server. You might end up blocking your own transaction consuming valuable resources on the server and so on.

Another technique is to use a table variable to store the results from the inner SP. Since table variables do not participate in user transactions, any subsequent COMMIT or ROLLBACK will not affect the data in the table variable. You can then retrieve the rows from the table variable after the transaction or in the CATCH block successfully. This will work from SQL Server 2000 onwards except there is no TRY...CATCH there.

|||

Hello everybody,

thankx for the answers .

By the way I found another solution that solve my problem but because SQL server programming is not my main skill I would like to know what to you think about it.

select @.@.trancount, 'Entering'

begin transaction

begin try

-- Detect if the procedure was called from an active transaction and save that for later use.In the procedure, @.TranCounter = 0 means there was no active transaction and the procedure started one.

-- @.TranCounter > 0 means an active transaction was started before the procedure was called.

DECLARE @.TranCounter INT;

DECLARE @.i INT;

SET @.TranCounter = @.@.TRANCOUNT;

select @.TranCounter,'Entering s.p. 1st level from broker'

IF @.TranCounter > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave;

select @.@.trancount, 'Save transaction SP 1st level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 1st level from broker'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE FIRST LEVEL S.P.

--this is an another nested SP

DECLARE @.TranCounterSP2 INT;

SET @.TranCounterSP2 = @.@.TRANCOUNT;

select @.TranCounterSP2,'Entering s.p. 2nd level from s.p. 1st level'

IF @.TranCounterSP2 > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave2;

select @.@.trancount, 'Save transaction SP 2nd level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 2nd level from sp 1st level'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE SECOND LEVEL S.P.

IF @.TranCounterSP2 = 0

BEGIN

COMMIT TRANSACTION

END

select @.@.trancount, 'S.p. 2nd level was committed'

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 2nd level transaction on going to be rolled back rolled back'

IF @.TranCounterSP2 = 0

-- Transaction started in procedure. Roll back complete transaction - included the outer one.

ROLLBACK TRANSACTION;

ELSE

IF XACT_STATE() <> -1

ROLLBACK TRANSACTION ProcedureSave2;select @.@.trancount, 'SP 2nd level transaction was rolled back'

END CATCH

--this is the end of an another nested SP

-- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called.

IF @.TranCounter = 0

-- @.TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 1st level transaction on going to be rolled back rolled back'

-- An error occurred; must determine which type of rollback will roll back only the work done in the procedure.

IF @.TranCounter = 0

-- Transaction started in procedure. Roll back complete transaction (INCLUDED the outer TRANSACTION).

ROLLBACK TRANSACTION;

ELSE

-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.

IF XACT_STATE() <> -1

-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.

ROLLBACK TRANSACTION ProcedureSave;

select @.@.trancount, 'SP 1st level transaction was rolled back'

END CATCH

commit transaction -- for the outer transaction

select @.@.trancount, 'Outer transaction was committed'

end try

begin catch

if @.@.trancount>0

begin

rollback transaction

select @.@.trancount, 'Outer transaction was rolled back'

end

end catch

select @.@.trancount, 'Outer'

If I try this script everything works as I want, somebody can also confirm me that the transaction are also properly define and I'm not going to execute code in the wrong one?

Thank you

Marina B.

Nested Transaction!

The following example is given in BOL under the topic 'Nested
Transaction':
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction,this will roll back TransProc's
nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit of the inner
transaction from the first EXECUTE statement of TransProc was
overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
---
The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
Apart from this, there is another transaction named 'OutOfProc' (which,
if I am not mistaken, isn't a part & parcel of the stored procedure
'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
where or which is the nested transaction? Shouldn't a nested
transaction have 1 transaction under another transaction something like
this (similar to nested If...Else conditions):
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
--Do Something
BEGIN TRANSACTION InProc1
--Do Something More
BEGIN TRANSACTION InProc2
--Do Something More
COMMIT TRANSACTION InProc
---
Or does the presence of more than 1 SQL statement (the 2 INSERT
queries) within the transaction 'InProc' (in the stored procedure
'TransProc') make it a nested transaction?
Thanks,
ArpanA begin tran must have either a commit/rollback tran. Thus, the TransProc's
Inproc transaction is participating in the OutProc transaction. If
commit/rollback tran OutProc is not explicitly called and the connection to
the server is dropped, the transaction will be forced to rollback.
As stated in bol, if outer most commit/rollback is what really important. It
decides the final commit all or rollback all.
-oj
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Thanks, OJ, for your help. How stupid of me....actually I had
misinterpreted the example given in BOL :-)
Thanks once again,
Regards,
Arpan|||Transactions are not really nested. There can only be one outstanding
transaction context for a connection. The global variable @.@.TRANCOUNT
reports whether or not a transaction context is outstanding, and its value
immediately before a Transact-SQL statement begins executing determines
whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
0, no transaction context is outstanding for the connection. Each time a
BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
@.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
transaction started by that BEGIN TRANSACTION statement. Each sucessive
BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
COMMIT TRANSACTION statement are changes made within the transaction made
permanent by that COMMIT TRANSACTION statement. All Transact-SQL statements
that alter the state or schema of the database operate within the context of
a transaction. If a transaction context is not already outstanding, then a
new transaction context is initiated before executing the statement. If
IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
committed immediately after the statement completes. This is called
"autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit COMMIT
WORK must be issued to commit the transaction. A transaction can span
multiple statements, multiple stored procedure calls, even multiple batches.
If a transaction is outstanding, dynamic SQL executed via either EXEC() or
sp_executesql executes within that transaction context. Since changes made
while a transaction context is outstanding are not made permanent until
they're committed, ROLLBACK backs all of the changes for the entire
transaction context. The only exception is when a savepoint is specified on
a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
log that identifies a reference point which can be specified in a ROLLBACK
statement to partially backout changes made while a transaction context is
outstanding. When a ROLLBACK savepoint statement is executed, all changes
made after the save point are backed out, and the transaction context
remains outstanding.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Dude, paragraph breaks :)

> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection.
I don't know that this particularly means that transactions aren't nested.
By your definition IF..THEN statements are not really nested. I don't think
that nested transactions implies any technical innerworkings more than it
just simply implies that you can syntactically do:
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
It is more or less meaningless to us whether a stack is used or a counter
and whether or not ROLLBACK kills the whole stack or just goes back to the
original point. Either way I still consider them nested because of syntax.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection. The global variable @.@.TRANCOUNT
> reports whether or not a transaction context is outstanding, and its value
> immediately before a Transact-SQL statement begins executing determines
> whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
> 0, no transaction context is outstanding for the connection. Each time a
> BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
> @.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
> transaction started by that BEGIN TRANSACTION statement. Each sucessive
> BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
> decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
> COMMIT TRANSACTION statement are changes made within the transaction made
> permanent by that COMMIT TRANSACTION statement. All Transact-SQL
> statements
> that alter the state or schema of the database operate within the context
> of
> a transaction. If a transaction context is not already outstanding, then
> a
> new transaction context is initiated before executing the statement. If
> IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
> committed immediately after the statement completes. This is called
> "autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit
> COMMIT
> WORK must be issued to commit the transaction. A transaction can span
> multiple statements, multiple stored procedure calls, even multiple
> batches.
> If a transaction is outstanding, dynamic SQL executed via either EXEC() or
> sp_executesql executes within that transaction context. Since changes
> made
> while a transaction context is outstanding are not made permanent until
> they're committed, ROLLBACK backs all of the changes for the entire
> transaction context. The only exception is when a savepoint is specified
> on
> a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
> log that identifies a reference point which can be specified in a ROLLBACK
> statement to partially backout changes made while a transaction context is
> outstanding. When a ROLLBACK savepoint statement is executed, all changes
> made after the save point are backed out, and the transaction context
> remains outstanding.
>
> "Arpan" <arpan_de@.hotmail.com> wrote in message
> news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
>|||There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there m
ust always be a matching commit transaction for every begin transaction, and
that transactions can be "nested," many newbies erroneously assume that a r
ollback only backs out the innermost block of statements--that is, to the po
int of the innermost BEGIN TRANSACTION. The assumption follows from the pat
tern etched in their brain by the second example. A break exits the innermo
st block; therefore, it is logical to assume (erroneously, of course) that a
rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the b
lock of code bounded by the outermost begin/commit pair, the transaction con
text is initiated by the first BEGIN TRANSACTION and is terminated either by
any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:#7aRUwomFHA.2484@.TK2
MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't thi
nk
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
>
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
>
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of syntax
.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>
>|||I like to think of it as:
"Nested transactions are allowed in syntax but not semantics."
I don't know what "real" nested transactions mean, or if there is a formal d
efinition of what nested
transaction semantics means? :
Perhaps a rollback of an inner transaction would allow commit of an outer tr
ansaction? We can do
that with savepoints.
Or the other way: A commit of an inner transaction will still be committed i
f the outer transaction
does a rollback? True, we don't have this in SQL server (which, I believe, B
rian wished for in an
earlier post in some other thread). To some extent, we can work around it wi
th table variables or
opening a new connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
By your definition
> IF..THEN statements are not really nested. I don't think that nested tran
sactions implies any
> technical innerworkings more than it just simply implies that you can synt
actically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
and whether or not
> ROLLBACK kills the whole stack or just goes back to the original point. E
ither way I still
> consider them nested because of syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||But the one break can get us out of several scope operators. I see your
point, but as long as you have to commit them one at a time, I think the
common term nested for transactions is probably going to stick...
And you code still looks pretty good in plain text :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:u9My2XpmFHA.4028@.TK2MSFTNGP10.phx.gbl...
There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there
must always be a matching commit transaction for every begin transaction,
and that transactions can be "nested," many newbies erroneously assume that
a rollback only backs out the innermost block of statements--that is, to the
point of the innermost BEGIN TRANSACTION. The assumption follows from the
pattern etched in their brain by the second example. A break exits the
innermost block; therefore, it is logical to assume (erroneously, of course)
that a rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the
block of code bounded by the outermost begin/commit pair, the transaction
context is initiated by the first BEGIN TRANSACTION and is terminated either
by any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:#7aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't
> think
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of
> syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||No doubt we could use a more convienient model for how transactions work,
but your statement is great.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDRWzWqmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I like to think of it as:
> "Nested transactions are allowed in syntax but not semantics."
>
> I don't know what "real" nested transactions mean, or if there is a formal
> definition of what nested transaction semantics means? :
> Perhaps a rollback of an inner transaction would allow commit of an outer
> transaction? We can do that with savepoints.
> Or the other way: A commit of an inner transaction will still be committed
> if the outer transaction does a rollback? True, we don't have this in SQL
> server (which, I believe, Brian wished for in an earlier post in some
> other thread). To some extent, we can work around it with table variables
> or opening a new connection.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>sql

Nested Stored Procs w/Transactions

I have some nested stored procedures where one sp calls another, etc. I nee
d
this wrapped in a transaction so that if an error occurs on any one sp
(either the calling sp or the one that is called) it will fail.
I'm continuall getting this error: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 2, current count = 3." So I've been playing around with
where to put the Begin Tran, Committ,
Rollback, etc.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
----
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer
and spInsert_Trans to be their own transaction as I call these sps by
themselves
elsewhere in my application.Hi,
have you tried this:
create procedure dbo.spFile_Process
as
set implicit_transactions off
--do something here without transaction
begin tran OuterTran
exec spInsert_Customer
exec spInsert_Trans
commit OuterTran
both nested procedures should have the same construction:
create procedure spInsert_Customer
as
set implicit_transactions off
begin tran TranA
...
commit TranA
create procedure spInsert_Trans
as
set implicit_transactions off
begin tran TranB
...
commit TranB
in case of error you should rollback the transation you are within
(decreasing @.@.trancount) and possible outer transactions. you have to
specify name of the transaction when rolling back, otherwise you will roll
back all transactions you're in.
HTH
Peter|||Alternately you could check for the existance of a transaction at the
begining of each procedure and only open a new transaction within the
procedure if one does not exist:
IF @.@.Trancount != 0
set @.Dotran = 0
...
If @.Dotran = 1
begin tran
...
-- on error
if @.dotran = 1
rollback transaction
return @.error
-- on success
if @.dotran = 1
comit tran
return 0
"Rogas69" wrote:

> Hi,
> have you tried this:
> create procedure dbo.spFile_Process
> as
> set implicit_transactions off
> --do something here without transaction
> begin tran OuterTran
> exec spInsert_Customer
> exec spInsert_Trans
> commit OuterTran
> both nested procedures should have the same construction:
> create procedure spInsert_Customer
> as
> set implicit_transactions off
> begin tran TranA
> ...
> commit TranA
> create procedure spInsert_Trans
> as
> set implicit_transactions off
> begin tran TranB
> ...
> commit TranB
> in case of error you should rollback the transation you are within
> (decreasing @.@.trancount) and possible outer transactions. you have to
> specify name of the transaction when rolling back, otherwise you will roll
> back all transactions you're in.
> HTH
> Peter
>
>|||Aren't you forgetting :
if @.@.Trancount = 0
set@.DoTran = 1
Anyway...
I've gone ahead and added the following whenever the committ tran, begin
tran, or rollback tran appear in my stored procs:
IF @.@.TRANCOUNT > 0 AND @.@.ERROR <> 0 BEGIN
ROLLBACK TRAN
RETURN
END
IF @.@.TRANCOUNT > 0 AND @.@.ERROR = 0 BEGIN
COMMIT TRAN
END
Now I'm longer getting the error message, but the Transaction is not being
rolled back either.
"Tony Sellars" wrote:
> Alternately you could check for the existance of a transaction at the
> begining of each procedure and only open a new transaction within the
> procedure if one does not exist:
> IF @.@.Trancount != 0
> set @.Dotran = 0
> ...
> If @.Dotran = 1
> begin tran
> ...
> -- on error
> if @.dotran = 1
> rollback transaction
> return @.error
> -- on success
> if @.dotran = 1
> comit tran
> return 0
>
> "Rogas69" wrote:
>

Nested Distributed Transactions?

Hi All,

Is it possible to have a Distributed Transaction running within another distributed transaction and so on.

I have 3 servers(Servers A, B, and C). Server A has a linked server to Server B and Server B has a linked server to Server C. I have two triggers. One that resides on Server A and Updates Server B and Another on Server B that updates Server C. Trigger on Server A invokes a distributed transaction with Server B to Update it which in turn fires the trigger on Server B which invokes another distributed transaction with Server C.

Now is this possible?? If not how can I get Around this?? I can not just a distributed transaction on the trigger which resides on server A as the one on Server B can fire on its own and not always because of Server A.

Is it possible to have nested distributed transactions like Begin/Ends??

Thanks
AnthonyShould be OK.

Every trigger runs within a transaction and this should be automatically upgraded to a distributed transaction for cross server operations so you shouldn't have to do any coding aprt from the set xact_abort on.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested Database Transactions in Forms

This should be a fairly simple question. It's based on this error message:

"Transaction count after EXECUTEindicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.Previous count = 1, current count = 0."

I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want.

I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:

BEGIN

BEGIN TRY
BEGIN TRANSACTION
RAISERROR('The item selected does not exist in the database.', 16, 1);
COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block
END TRY

BEGIN CATCH
ROLLBACK
DECLARE @.ErrorSeverity INT, @.ErrorMessage NVARCHAR(4000)
SET @.ErrorSeverity = ERROR_SEVERITY()
SET @.ErrorMessage = ERROR_MESSAGE()
RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)
END CATCH

END

Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query:

con.Open();
SqlTransaction transaction = con.BeginTransaction();
command.Transaction = transaction;

try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
con.Close();
}

I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count.

Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning.

Thanks,

-Dan

In playing around with it, I've come to learn that the Exception.Message is the concatenation of any messages the exception holds. So, my CATCH block could contain this instead:

Label.Text = String.Format("Error: {0}", ex.Errors[0].Message);

That only gives the first error, which is the one I want (the one specified in RAISERROR). I just don't know if this is what I should do. Is that transaction count message something I should be concerned about, or will that always happen? Should I just use the first message in the exception?

Thanks.

Nest transactions in SQLServer

hi guys:
i am doing a hard work of migrating programs from Oracle to SQL-Server.
i encounte a problem about transaction which hardly hurt my heart (forgive
my poor english first ... :-) )
you know,there is such a usage in oracle:
SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACKTOSAVEPOINT xx
end if;
so, if things 2 failed, the data altered by things1 is guaranteed to be
rollback.
but when i change it to fit sql-server, i was happily wrote such codes:
BEGIN TRANS... //must
...
...
BEGIN TRANS... //SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
end if;
..
...
COMMIT TRANS.
yes, i got a error: 'Cannot start more transactions on this session' (oh,
these codes are under ado in delphi)
i find the answer in msdn
CAUSE
By design, OLE DB Provider for SQL Server does not allow nested
transactions.
http://support.microsoft.com/defaul...kb;en-us;316872
but,i think, such nest transcations (or same other trick equal to oracle
save point) is very needed in general business process.
how to solve this problem?
my best wishes.Hi
Have you looked up SAVE TRANSACTION in BOL?
John
"MaHahaXixi" wrote:

> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart (forgi
ve
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||hi john:
thank u for ur response.
what do u mean by BOL' sorroy, i am just a greenhand.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||oh... yes i got the answer
there are
save trans {savepoint}
rollack trans {savepoint}
in t-sql too.
thanks all the same
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3fh2p$vuo$1@.mail.cn99.com...
> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart
(forgive
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||thank you john, i got it .yes, save transactions
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||BOL = SQL Server Books Online, found in the SQL Server program group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message news:d3ftsf$137e$1@.mail.cn99.com...

> hi john:
> thank u for ur response.
> what do u mean by BOL' sorroy, i am just a greenhand.
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> SQL-Server.
> (forgive
> be
> codes:
> (oh,
>|||Hi
You may find these articles useful:
http://www.microsoft.com/resources/...r />
0761.mspx
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
John
MaHahaXixi wrote:
> oh... yes i got the answer
> there are
> save trans {savepoint}
> rollack trans {savepoint}
> in t-sql too.
> thanks all the same
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
> news:d3fh2p$vuo$1@.mail.cn99.com...
SQL-Server.
> (forgive
to be
codes:
session' (oh,
oracle|||thanks a lot :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23uTmv2zPFHA.3928@.TK2MSFTNGP09.phx.gbl...
> BOL = SQL Server Books Online, found in the SQL Server program group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3ftsf$137e$1@.mail.cn99.com...
to
oracle
>|||thank you! very useful to me!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113307105.002786.136380@.f14g2000cwb.googlegroups.com...
> Hi
> You may find these articles useful:
>
http://www.microsoft.com/resources/...art2/c0761.mspx

> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> John
> MaHahaXixi wrote:
> SQL-Server.
> to be
> codes:
> session' (oh,
> oracle
>

Need to Shrink Transaction Log

Hi everyone,
I urgently need to shrink the transaction log on our MSDE2000 database. It's
coming up to 3gig (for a 25mb database) and we're running out of room on the
server. I think it's using Full Recovery, but more on that in a moment.
Just wondering if someone could go over my plan and point out anything I
might be thinking of doing that might not work?
First of all, I want to make a complete backup of the database, just in case
anything breaks, then backup the log with truncate only, then do another full
backup, then use DBCC ShrinkFile...
In osql (db name is danV2)...

> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
> BACKUP LOG danV2 WITH TRUNCATE_ONLY
> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
> DBCC SHRINKFILE(danV2, 5)
does that look right?!
Although we're using Full Recovery mode on the Logs, but we don't have a
watertight strategy for failsafe.
I'm wondering if the best thing to do is to set the LOG files to SIMPLE, and
then run an Osql backup command every night!? That way, we can always restore
to the previous day if anything drastic happens.
How can I change the recovery mode for the database using OSQL?!
Thanks
Dan
Hello,
You steps looks good to shrik the LDF file. To chnage the recovery model in
OSQL use the below command.
ALTER DATABASE danV2 SET RECOVERY SIMPLE
Probably after shrinking the file. If you do a Log backup in frequent
intervals you could have LDF file not growing
as well this will help you to recover the database when required. Just setup
the backup strategy based on your data criticality.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
> Hi everyone,
> I urgently need to shrink the transaction log on our MSDE2000 database.
> It's
> coming up to 3gig (for a 25mb database) and we're running out of room on
> the
> server. I think it's using Full Recovery, but more on that in a moment.
> Just wondering if someone could go over my plan and point out anything I
> might be thinking of doing that might not work?
> First of all, I want to make a complete backup of the database, just in
> case
> anything breaks, then backup the log with truncate only, then do another
> full
> backup, then use DBCC ShrinkFile...
> In osql (db name is danV2)...
>
> does that look right?!
> Although we're using Full Recovery mode on the Logs, but we don't have a
> watertight strategy for failsafe.
> I'm wondering if the best thing to do is to set the LOG files to SIMPLE,
> and
> then run an Osql backup command every night!? That way, we can always
> restore
> to the previous day if anything drastic happens.
> How can I change the recovery mode for the database using OSQL?!
> Thanks
>
> Dan
>
|||Hari,
Thanks for that, although you're answer has given me food for thought, and
therefore more questions!
If I my plan and leave the database at full recovery, and then backup the
log file periodically,
1) how often should I do that (daily / weekly / monthly) ?
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
3) do I need to keep all the transaction log backups?
I'm thinking... weekly full backup on a sunday, then daily log file backups.
Would I just need to create 6 backup files (for the week), just the last one,
or more than 6?!
Thanks again!
Dan
"Hari Prasad" wrote:

> Hello,
> You steps looks good to shrik the LDF file. To chnage the recovery model in
> OSQL use the below command.
> ALTER DATABASE danV2 SET RECOVERY SIMPLE
> Probably after shrinking the file. If you do a Log backup in frequent
> intervals you could have LDF file not growing
> as well this will help you to recover the database when required. Just setup
> the backup strategy based on your data criticality.
> Thanks
> Hari
>
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
>
>
|||"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
What are your needs?
At my old job, we did them every 15 minutes.
So basically ask yourself "how much work can you afford to lose and how much
time/effort do you want to spend recovering it?"

> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
No. Don't do this.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> 3) do I need to keep all the transaction log backups?
>
You need to keep at least everything since the last full backup. So the
recovery would be restore full backup (WITH NORECOVERY) and then restore
subsequent transaction logs.
Generally I'd keep several full backups with all subsequent transaction
logs.
Again, the particular answer depends on the churn of your data. If you
replace all the data in your database every other day (we basically had a DB
like that), then full backups going back a week may be overkill.
On the other hand, if the data changes infrequently, or there's auditing
required, you may need to keep backups going back years.

> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
I'd probably do a full backup once a week and then 2-3 log backups during
the day, just to keep potential data loss down, but have a happy medium when
it comes to how much work I want to do to recover data.

> Thanks again!
>
> Dan
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hello,
Answer to your queries:-
1) how often should I do that (daily / weekly / monthly) ?
Transaction log backup needs to beaked up every 30 minutes atleast. This
will make sure you have log backup for recovery and LDF wil l not grow.
With the log backup during a database crash you can recover the database
atleast till last log backup. Means u maximum loose 29 minutes data.
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
No need to shrink the LDF file. If you do 30 minutes log backup after the
backup the LDF file will be cleared automatically. So no need to SHRINK the
LDF file.
3) do I need to keep all the transaction log backups?
All the log backups after the last FULL database backup. So do a FULL
database backup every night and clear all the old log backup files.
My best recommendation is dality FULL database backup during night and
subsequent 30 minutes transactioin log backup will be good for you.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...[vbcol=seagreen]
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> 3) do I need to keep all the transaction log backups?
> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
> Thanks again!
>
> Dan
>
>
> "Hari Prasad" wrote:
|||Thanks everyone,
Very useful information from all of you - I feel much clearer now.
One final question... we're currently using MSDE as our database (currently
setting up an SQL Server machine), but until then, how do I setup these
nightly full/periodic log backups? (no Enterprise Manager!)
Thanks,
Dan
"Hari Prasad" wrote:

> Hello,
> Answer to your queries:-
> 1) how often should I do that (daily / weekly / monthly) ?
> Transaction log backup needs to beaked up every 30 minutes atleast. This
> will make sure you have log backup for recovery and LDF wil l not grow.
> With the log backup during a database crash you can recover the database
> atleast till last log backup. Means u maximum loose 29 minutes data.
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> No need to shrink the LDF file. If you do 30 minutes log backup after the
> backup the LDF file will be cleared automatically. So no need to SHRINK the
> LDF file.
> 3) do I need to keep all the transaction log backups?
> All the log backups after the last FULL database backup. So do a FULL
> database backup every night and clear all the old log backup files.
> My best recommendation is dality FULL database backup during night and
> subsequent 30 minutes transactioin log backup will be good for you.
> Thanks
> Hari
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
>
>
|||Hi, Dan,
You can use OSQL command and the stored procedures "sp_add_job",
"sp_add_jobstep ", "sp_add_jobserver ", "sp_start_job" and
"sp_add_jobschedule" to periodically back up your database.
I recommend that you first create a script file to use the stored
procedures to schedule your backup job; and then use OSQL command to run
the script file. Of course, you need to ensure that the SQLSERVERAGENT
service has been started.
For the detailed information, please refer to:
How to back up a Microsoft Data Engine database by using Transact-SQL
http://support.microsoft.com/kb/241397/EN-US/
How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005
Express Edition by using the osql utility
http://support.microsoft.com/kb/325003
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Dan,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Have a great day!
Charles Wang
Microsoft Online Partner Support
|||Not being a trained DBA, but experienced in SQL servers, this
information has helped me. But...
In order to keep things simple and easy I am trying to stick to using
Maintenance Plans, instead of scripting my own jobs.
One of my DBs is about 5GB and the transaction log often grows to 8GB.
I've found out that the cause is the optimization process.
What is the best practice dealing with this issue? I can do 30 minute
T-Log backups, which are small (<50 meg) except for the one after the
optimization.
I'm not meaning to threadjack into an optimization discussion. But it
seems like the Maintenance Plan wizard method is going to force the
T-Log to be large no matter what you do. Should I accept the T-Log size
and move on or is there an optimization method better than the MP?
*** Sent via Developersdex http://www.codecomments.com ***

Need to Shrink Transaction Log

Hi everyone,
I urgently need to shrink the transaction log on our MSDE2000 database. It's
coming up to 3gig (for a 25mb database) and we're running out of room on the
server. I think it's using Full Recovery, but more on that in a moment.
Just wondering if someone could go over my plan and point out anything I
might be thinking of doing that might not work?
First of all, I want to make a complete backup of the database, just in case
anything breaks, then backup the log with truncate only, then do another ful
l
backup, then use DBCC ShrinkFile...
In osql (db name is danV2)...

> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
> BACKUP LOG danV2 WITH TRUNCATE_ONLY
> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
> DBCC SHRINKFILE(danV2, 5)
does that look right?!
Although we're using Full Recovery mode on the Logs, but we don't have a
watertight strategy for failsafe.
I'm wondering if the best thing to do is to set the LOG files to SIMPLE, and
then run an Osql backup command every night!? That way, we can always restor
e
to the previous day if anything drastic happens.
How can I change the recovery mode for the database using OSQL?!
Thanks
DanHello,
You steps looks good to shrik the LDF file. To chnage the recovery model in
OSQL use the below command.
ALTER DATABASE danV2 SET RECOVERY SIMPLE
Probably after shrinking the file. If you do a Log backup in frequent
intervals you could have LDF file not growing
as well this will help you to recover the database when required. Just setup
the backup strategy based on your data criticality.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
> Hi everyone,
> I urgently need to shrink the transaction log on our MSDE2000 database.
> It's
> coming up to 3gig (for a 25mb database) and we're running out of room on
> the
> server. I think it's using Full Recovery, but more on that in a moment.
> Just wondering if someone could go over my plan and point out anything I
> might be thinking of doing that might not work?
> First of all, I want to make a complete backup of the database, just in
> case
> anything breaks, then backup the log with truncate only, then do another
> full
> backup, then use DBCC ShrinkFile...
> In osql (db name is danV2)...
>
> does that look right?!
> Although we're using Full Recovery mode on the Logs, but we don't have a
> watertight strategy for failsafe.
> I'm wondering if the best thing to do is to set the LOG files to SIMPLE,
> and
> then run an Osql backup command every night!? That way, we can always
> restore
> to the previous day if anything drastic happens.
> How can I change the recovery mode for the database using OSQL?!
> Thanks
>
> Dan
>|||Hari,
Thanks for that, although you're answer has given me food for thought, and
therefore more questions!
If I my plan and leave the database at full recovery, and then backup the
log file periodically,
1) how often should I do that (daily / weekly / monthly) ?
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
3) do I need to keep all the transaction log backups?
I'm thinking... weekly full backup on a sunday, then daily log file backups.
Would I just need to create 6 backup files (for the week), just the last one
,
or more than 6?!
Thanks again!
Dan
"Hari Prasad" wrote:

> Hello,
> You steps looks good to shrik the LDF file. To chnage the recovery model i
n
> OSQL use the below command.
> ALTER DATABASE danV2 SET RECOVERY SIMPLE
> Probably after shrinking the file. If you do a Log backup in frequent
> intervals you could have LDF file not growing
> as well this will help you to recover the database when required. Just set
up
> the backup strategy based on your data criticality.
> Thanks
> Hari
>
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
>
>|||"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
What are your needs?
At my old job, we did them every 15 minutes.
So basically ask yourself "how much work can you afford to lose and how much
time/effort do you want to spend recovering it?"

> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
No. Don't do this.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> 3) do I need to keep all the transaction log backups?
>
You need to keep at least everything since the last full backup. So the
recovery would be restore full backup (WITH NORECOVERY) and then restore
subsequent transaction logs.
Generally I'd keep several full backups with all subsequent transaction
logs.
Again, the particular answer depends on the churn of your data. If you
replace all the data in your database every other day (we basically had a DB
like that), then full backups going back a week may be overkill.
On the other hand, if the data changes infrequently, or there's auditing
required, you may need to keep backups going back years.

> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
I'd probably do a full backup once a week and then 2-3 log backups during
the day, just to keep potential data loss down, but have a happy medium when
it comes to how much work I want to do to recover data.

> Thanks again!
>
> Dan
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
Answer to your queries:-
1) how often should I do that (daily / weekly / monthly) ?
Transaction log backup needs to beaked up every 30 minutes atleast. This
will make sure you have log backup for recovery and LDF wil l not grow.
With the log backup during a database crash you can recover the database
atleast till last log backup. Means u maximum loose 29 minutes data.
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
No need to shrink the LDF file. If you do 30 minutes log backup after the
backup the LDF file will be cleared automatically. So no need to SHRINK the
LDF file.
3) do I need to keep all the transaction log backups?
All the log backups after the last FULL database backup. So do a FULL
database backup every night and clear all the old log backup files.
My best recommendation is dality FULL database backup during night and
subsequent 30 minutes transactioin log backup will be good for you.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...[vbcol=seagreen]
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> 3) do I need to keep all the transaction log backups?
> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
> Thanks again!
>
> Dan
>
>
> "Hari Prasad" wrote:
>|||>> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or">
> No. Don't do this.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And here's another way of putting it:
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23UeKDacXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> What are your needs?
> At my old job, we did them every 15 minutes.
> So basically ask yourself "how much work can you afford to lose and how mu
ch time/effort do you
> want to spend recovering it?"
>
> No. Don't do this.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> You need to keep at least everything since the last full backup. So the r
ecovery would be restore
> full backup (WITH NORECOVERY) and then restore subsequent transaction logs
.
> Generally I'd keep several full backups with all subsequent transaction lo
gs.
> Again, the particular answer depends on the churn of your data. If you re
place all the data in
> your database every other day (we basically had a DB like that), then full
backups going back a
> week may be overkill.
> On the other hand, if the data changes infrequently, or there's auditing r
equired, you may need to
> keep backups going back years.
>
> I'd probably do a full backup once a week and then 2-3 log backups during
the day, just to keep
> potential data loss down, but have a happy medium when it comes to how muc
h work I want to do to
> recover data.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||Thanks everyone,
Very useful information from all of you - I feel much clearer now.
One final question... we're currently using MSDE as our database (currently
setting up an SQL Server machine), but until then, how do I setup these
nightly full/periodic log backups' (no Enterprise Manager!)
Thanks,
Dan
"Hari Prasad" wrote:

> Hello,
> Answer to your queries:-
> 1) how often should I do that (daily / weekly / monthly) ?
> Transaction log backup needs to beaked up every 30 minutes atleast. This
> will make sure you have log backup for recovery and LDF wil l not grow.
> With the log backup during a database crash you can recover the database
> atleast till last log backup. Means u maximum loose 29 minutes data.
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> No need to shrink the LDF file. If you do 30 minutes log backup after the
> backup the LDF file will be cleared automatically. So no need to SHRINK th
e
> LDF file.
> 3) do I need to keep all the transaction log backups?
> All the log backups after the last FULL database backup. So do a FULL
> database backup every night and clear all the old log backup files.
> My best recommendation is dality FULL database backup during night and
> subsequent 30 minutes transactioin log backup will be good for you.
> Thanks
> Hari
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
>
>|||Hi, Dan,
You can use OSQL command and the stored procedures "sp_add_job",
"sp_add_jobstep ", "sp_add_jobserver ", "sp_start_job" and
"sp_add_jobschedule" to periodically back up your database.
I recommend that you first create a script file to use the stored
procedures to schedule your backup job; and then use OSQL command to run
the script file. Of course, you need to ensure that the SQLSERVERAGENT
service has been started.
For the detailed information, please refer to:
How to back up a Microsoft Data Engine database by using Transact-SQL
http://support.microsoft.com/kb/241397/EN-US/
How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005
Express Edition by using the osql utility
http://support.microsoft.com/kb/325003
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Dan,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Have a great day!
Charles Wang
Microsoft Online Partner Support|||Not being a trained DBA, but experienced in SQL servers, this
information has helped me. But...
In order to keep things simple and easy I am trying to stick to using
Maintenance Plans, instead of scripting my own jobs.
One of my DBs is about 5GB and the transaction log often grows to 8GB.
I've found out that the cause is the optimization process.
What is the best practice dealing with this issue? I can do 30 minute
T-Log backups, which are small (<50 meg) except for the one after the
optimization.
I'm not meaning to threadjack into an optimization discussion. But it
seems like the Maintenance Plan wizard method is going to force the
T-Log to be large no matter what you do. Should I accept the T-Log size
and move on or is there an optimization method better than the MP?
*** Sent via Developersdex http://www.codecomments.com ***