Best practices to design datawarehouse for a Fraud Detection/Transaction Monitoring Application

Hi All,

This thread relates to following thread of Architecure forum.

http://forums.microsoft.com/msdn/showpost.aspx?postid=1908857&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

Because after reaching at decision that application would require datawarehousing the members suggested that it would be nice if it is posted on datawarehouse forum.

I would appreciate if could go through the mentioned thread that would state application's business requirement.

Now i am continuing the from the thread where we were discussing about SCD.

In our Application customer address is defined in 6 fields (Street,city,State,Country,Pincode,ContactNo) so if we are to maintain 3 type of addresses like Office,Communication,Residence then we have to add 18 fields in our Customer Master dimension table in our dataware house.

Suppose after some time customer office address is changed so what should be done in Customer Master Table at time of synchronization, Should ETL process insert a new record into dimension table with updated address in office address fields or it should update the existing record fields without adding new one.

If for Changing dimensions we are adding a new record then we have to maintain a flag field where status of record would be kept like 'current record' or 'old record' , this would help us to identify the main record.Every time whenever a new customer record is added old record status is changed to 'Old record'.

Should we add new record for changing fields like address,designation,employer,annual income etc or update the existing record only.Because in our analysis these all fields would play major because after each profile updation a risk category is assigned to customer.So we can not afford to overwrite any field information because at any time old information can be asked by management/regulator.

If every time for each field updation a new record is updated then customer master datawarehouse table would become 5 times if each customer record is updated five times during the year.

Suggest us the efficient approach to handle changing dimensions.

Thanks & Regards,

Sameer Gautam

Hi,

You don't have to create a record each time a change happens, just depends if you want to capture the history for that field or not. For example you may want to capture history for change of address, so you would add a new record, but you may not want it for surname, so just update all records for that customer with the new surname.

SCD are a pain to say the least when data warehousing, the principle is nice but coding it is sometimes complicated especially if you try and use snowflakes. And space soon becomes an issue, if you data changes regually. And you are right that if you make a change to a customer 5 times in a year then you will get 5 new records. Although you may code it so it only captures the end of year snapshot, rather than every change.

I would have a look through some of kimballs articles:

http://kimballgroup.com/html/articlesArchitecture/articlesAdvancedDim.html

Good luck

Matt

sql

Best Practices for Reducing Transaction Log Sizes?

Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!
Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegro ups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>

Best Practices for Reducing Transaction Log Sizes?

Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>

Best Practices - SQL Transactions

I have a web app (ASP) that does all updates, inserts by calling
transaction-supported COM+ components (with the transaction started in the
ASP page, i.e. transaction=required) that use ADO to call stored procedures
(that usually involve single tables). If there is any error (missing SP,
parameter value of wrong type, etc.) with the database insert/update, MTS
automatically rolls back everything that was done in the database (and maybe
SQL Server does that with any error in an SP anyway'). As such, when I
write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
So, I have many SPs (that do not return any indication of success, or not)
like
INSERT INTO Table
(ColumnA, ColumnB)
VALUES
(ValueA, ValueB)
WHERE
Some condition
As a matter of best practice, should SQL programmers always enclose INSERTs,
UPDATEs within transactional statements in a production database? Should one
always check for errors with INSERTS or UPDATES? Or, with errors like I
describe (but not with business logic), does SQL Server automatically
rollback everything in an SP? Or, should one just save those statements for
when the SQL script and logic itself takes care of rolling back a database
when a series of updates or inserts are made?
Thanks for any thoughts."Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES?
No,

>Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP?
No, but the client will get an error message and rollback.
Usually, stored procedures should not contain transactional logic. Let the
client take care of it.
David|||Don Miller wrote:
> As a matter of best practice, should SQL programmers always enclose INSERT
s,
> UPDATEs within transactional statements in a production database? Should o
ne
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements fo
r
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
My thoughts are:
1) Transactions are only required if there's more than one DML
statement (or SELECT statement that needs to maintain a lock).
2) Error checking should be done after any statement that can fail.
This includes all DML and DDL statements. Pretty much everything except
SELECTs (though I guess they could technically fail as well...).
Kris|||David wrote:
> Usually, stored procedures should not contain transactional logic. > Let the clie
nt take care of it.
Are you sure? Isn't it better to ensure your stored procedures are
transactionally correct regardless of where they are executed from?
Kris|||To my knowledge, an error is not sufficient for MTS to roll back: if you do
not call ObjectContext.SetAbort explicitely, MTS will think that the whole
operation has been successfull and will commit it; even if there have been
one or multiple errors.
In the same way, if there is an error inside a SP, SQL-Server will not
rollback the transaction automatically for you: you must check for any error
(@.@.error) and call the rollback operation yourself.
Even if the SP is already enrolled in a transaction, you still need to check
for a transaction error (@.@.error) if there is such a possibility inside the
SP and this, even if you have not included a BEGIN TRAN inside it.
To be clear, transactions and errors are not same: the fact that there have
been an error doesn't mean that the transaction will be or need to be
aborted.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
> for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>|||Don Miller (nospam@.nospam.com) writes:
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs, UPDATEs within transactional statements in a production
> database? Should one always check for errors with INSERTS or UPDATES?
> Or, with errors like I describe (but not with business logic), does SQL
> Server automatically rollback everything in an SP? Or, should one just
> save those statements for when the SQL script and logic itself takes
> care of rolling back a database when a series of updates or inserts are
> made?
If it's a single statement, there is no reason to have BEGIN/COMMIT
TRANSACTION around it, since the statement is a transaction in itself.
However, if you procedure performs several INSERT/UPDATE/DELETE statements
there should be a transaction around it. The procedure should not rely on
that the caller has set up a transaction. Sometimes you have a procedure
that you know is only performing part of a game. In this case, it is a
good habit to have this in the beginning:
IF @.@.trancount = 0
BEGIN
RAISERROR ('This procedure must be called with an active transaction',
16, 1)
RETURN 1
END
In SQL 2005, error checking in stored procedures can be handled with
TRY-CATCH. In SQL 2000, you need to check @.@.error, and if you have
started a transaction, you should rollback, since you know that you
were not able to fulfil your contract.
I have two articles on error handling in SQL Server on my web site.
http://www.sommarskog.se/error-handling-II.html gives more suggestions
on implementing error handling.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
The exception is when SET XACT_ABORT ON is active on the connection or proc.
SQL Server will then rollback the transaction and abort the batch when
runtime errors are encountered. However, compile errors are not affected
with XACT_ABORT ON so @.@.ERROR still needs to be checked when you want to
safeguard against all errors.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uUuZ5PNbGHA.1536@.TK2MSFTNGP02.phx.gbl...
> To my knowledge, an error is not sufficient for MTS to roll back: if you
> do not call ObjectContext.SetAbort explicitely, MTS will think that the
> whole operation has been successfull and will commit it; even if there
> have been one or multiple errors.
> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
> Even if the SP is already enrolled in a transaction, you still need to
> check for a transaction error (@.@.error) if there is such a possibility
> inside the SP and this, even if you have not included a BEGIN TRAN inside
> it.
>
> To be clear, transactions and errors are not same: the fact that there
> have been an error doesn't mean that the transaction will be or need to be
> aborted.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>|||<kriskirk@.hotmail.com> wrote in message
news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
> David wrote:
> Are you sure? Isn't it better to ensure your stored procedures are
> transactionally correct regardless of where they are executed from?
>
Ideally yes. Stored procedures should be atomic, consistent, and isolated.
They should typically not be durable because that makes assumptions about
where the procedure fits inside user transactions. But it requires quite a
bit of transaction handling code to make that happen.
Here's an example. A stored procedure should almost never issue a ROLLBACK
except to a savepoint. If it does then it can't be called in the scope of
an existing transaction. That might be OK for administrative stuff that you
know will be run from Management Studio, but for regular database
transactions.
create procedure foo
as
begin
begin transaction foo
begin try
'do work here
commit transaction
end try
begin catch
rollback transaction foo
commit transaction
exec usp_reraise_error
end catch
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4G3seSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> <kriskirk@.hotmail.com> wrote in message
> news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
>
> Ideally yes. Stored procedures should be atomic, consistent, and
> isolated. They should typically not be durable because that makes
> assumptions about where the procedure fits inside user transactions. But
> it requires quite a bit of transaction handling code to make that happen.
> Here's an example. A stored procedure should almost never issue a
> ROLLBACK except to a savepoint. If it does then it can't be called in the
> scope of an existing transaction. That might be OK for administrative
> stuff that you know will be run from Management Studio, but for regular
> database transactions.
>
Oops, here's a correction after morinng coffee.
create procedure foo
as
begin transaction
save transaction proc_scope
begin try
--DO WORK HERE
commit transaction
end try
begin catch
rollback transaction proc_scope
commit transaction
declare @.errormessage nvarchar(4000),
@.errorseverity int
select
@.errormessage = error_message(),
@.errorseverity = error_severity()
raiserror(@.errormessage, @.errorseverity, 1)
end catch
In SQL 2005 you can just cut and paste all this junk around your procedure,
and you don't have to pollute the implementation with a bunch of error
handling noise.
So there is a right way to do transaction handling in a stored proceudre,
and it isn't all that hard, but transacaction handling is basically the
responsibility of the client code.
David|||Thanks to all who have responded, although I'm still not quite sure whether
I should (or need to) revisit about 100 SPs I have (that are called from a
client transaction through COM+ - and yes, the client does the
ObjectContext.SetAbort duties). It does work today with rollbacks as
necessary and expected but I felt lazy by relying on ASP to start the
transaction and have the MTS blackbox take care of the details especially
when dealing with SQL Server. But I guess that's a feature ;)
And thanks to Erland Sommarskog for the very thoughtful piece about error
handling.
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
> I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
INSERTs,
> UPDATEs within transactional statements in a production database? Should
one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>

Best Performance?

Assume a heavy transaction environment.
Let's say we have the same database model,
same hardware, file usage, type of data, queries etc. on two servers.
Server A is configured with a RAID 5 on all the data disks (9 of them),
and Server B is configured with RAID 5 on the data disks (5 of them) but
transaction log and tempdb is separated to its own mirrored disks (4 of
them - 2 each).
Which SQL server will run faster in terms of query response time AND write
performance? Database in Server A or Server B?Assumimng disc io is the limiting factor then I would say B would be =most likely to outperform A. But even better would be lose the raid 5 =and use 0+1 on 4 discs. or possibly on 6 and lose the separation of =tempdb. Of course this will lose you some space over the raid 5, but =will perform better. Hardware raid vs S/W will alo make a difference. =How much separating tempdb gives you totally depends on the number of =queries generating large temporary results.
Opinions are likely to vary a lot and you haven't given much info to go =on.
Mike John
"Rick" <bob@.bob.net> wrote in message =news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> > Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of =them),
> and Server B is configured with RAID 5 on the data disks (5 of them) =but
> transaction log and tempdb is separated to its own mirrored disks (4 =of
> them - 2 each).
> > Which SQL server will run faster in terms of query response time AND =write
> performance? Database in Server A or Server B?
> >|||I completely agree with Mike John's remark to drop RAID5 completely,
certainly for a 'heavy transaction environment' as you describe. Check out
this link for some reasons why
http://www.miracleas.dk/BAARF/BAARF2.html
(OK it's Oracle/Unix, but it is as tru for the SQLServer/Win world too..)
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uRds7fpjDHA.2676@.TK2MSFTNGP11.phx.gbl...
Assumimng disc io is the limiting factor then I would say B would be most
likely to outperform A. But even better would be lose the raid 5 and use 0+1
on 4 discs. or possibly on 6 and lose the separation of tempdb. Of course
this will lose you some space over the raid 5, but will perform better.
Hardware raid vs S/W will alo make a difference. How much separating tempdb
gives you totally depends on the number of queries generating large
temporary results.
Opinions are likely to vary a lot and you haven't given much info to go on.
Mike John
"Rick" <bob@.bob.net> wrote in message
news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of them),
> and Server B is configured with RAID 5 on the data disks (5 of them) but
> transaction log and tempdb is separated to its own mirrored disks (4 of
> them - 2 each).
> Which SQL server will run faster in terms of query response time AND write
> performance? Database in Server A or Server B?
>|||On Thu, 9 Oct 2003 11:06:17 -0500, "Rick" <bob@.bob.net> wrote:
>Assume a heavy transaction environment.
> Let's say we have the same database model,
>same hardware, file usage, type of data, queries etc. on two servers.
>Server A is configured with a RAID 5 on all the data disks (9 of them),
>and Server B is configured with RAID 5 on the data disks (5 of them) but
>transaction log and tempdb is separated to its own mirrored disks (4 of
>them - 2 each).
>Which SQL server will run faster in terms of query response time AND write
>performance? Database in Server A or Server B?
Well, like the other guys said, one would probably anticipate B
running a bit faster, but it's also going to depend on how much data
is cached, that is, on RAM size, and just how heavily tempdb gets
used, and for that matter, how heavily the log gets used. If it were
100% simple queries, A might even win, for a more usual mix of 90%
simple queries, 5% complex queries, and 5% highly localized updates,
you're probably close to the break-even point, assuming you're not
running short of disk IO bandwidth or CPU in either case A or case B.
J.

behavior of SQL on joined queries

Hi all,

Currently our product has a setup that stores information about
transactions in a transaction table. Additionally, certain transactions
pertain to specific people, and extra information is stored in another
table. So for good or ill, things look like this right now:

create table TransactionHistory (
TrnID int identity (1,1),
TrnDT datetime,
--other information about a basic transaction goes here.
--All transactions have this info
Primary Key Clustered (TrnID)
)

Create Index TrnDTIndex on TransactionHistory(TrnDT)

create table PersonTransactionHistory (
TrnID int,
PersonID int,
--extended data pertaining only to "person" transactions goes
--here. only Person transactions have this
Primary Key Clustered(TrnID),
Foreign Key (TrnID) references TransactionHistory (TrnID)
)

Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

A query about a group of people over a certain date range might fetch
information like so:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

In my experience, this poses a real problem when trying to run queries
that uses both date and personID criteria. If my guesses are correct this
is because SQL is forced to do one of two things:

1 - Use TrnPersonIDIndex to find all transactions which match the person
criteria, then for each do a lookup in the PersonTransactionHistory to
fetch the TrnID and subsequently do a lookup of the TrnID in the clustered
index of the TransactionHistory Table, and finally determine if a given
transaction also matches the date time criteria.

2 - Use TrnDTIndex to final all transaction matching the date criteria,
and then perform lookups similar to the above, except for personID instead
of datetime.

Compounding this is my suspicion (based on performance comparison of when
I specify which indexes to use in the query vs when I let SQL Server
decide itself) that SQL sometimes chooses a very non optimal course. (Of
course, sometimes it chooses a better course than me - the point is I want
it to always be able to pick a good enough course such that I don't have
to bother specifying). Perhaps the table layout is making it difficult for
SQL Server to find a good query plan in all cases.

Basically I'm trying to determine ways to improve our table design here to
make reporting easier, as this gets painful when running report for
large groups of people during large date ranges. I see a few options based
on my above hypothesis, and am looking for comments and/or corrections.

1 - Add the TrnDT column to the PersonTransactionHistory Table as
well. Then create a foreign key relationship of PersonTransactionHistory
(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
indexes on PersonTransactionHistory with (TrnDT, PersonID) and
(PersonID, TrnDT). This seems like it would let SQL Server make
much more efficient execution plans. However, I am unsure if SQL server
can leverage the FK on TrnDT to use those new indexes if I give it a query
like:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

The trick being that SQL server would know that it can use PTH.TrnDT and
TH.TrnDT interchangably because of the foreign key (this would support all
the preexisting existing queries that explicitly named TH.TrnDT - any that
didn't explicitly specify the table would now have ambigious column
names...)

2 - Just coalesce the two tables into one. The original intent was to save
space by not requiring extra columns about Persons for all rows, many of
which did not have anything to do with a particular person (for instance a
contact point going active). In my experience with our product, the end
user's decisions about archiving and purging have a much bigger impact
than this, so in my opinion efficient querying is more important than
space. However I'm not sure if this is an elegant solution either. It also
might require more changes to existing code, although the use of views
might help.

We also run reports based on other criteria (columns I replaced with
comments above) but none of them are as problematic as the situation
above. However, it seems that if I can understand the best way to solve
this, I will be able to leverage that approach if other types of reports
become problematic.

Any opinions would be greatly appreciated. Also any references to good
sources regarding table and index design would be helpful as well (online
or offline references...)

thanks,
DaveMetal Dave (metal@.spam.spam) writes:
> create table TransactionHistory (
> TrnID int identity (1,1),
> TrnDT datetime,
> --other information about a basic transaction goes here.
> --All transactions have this info
> Primary Key Clustered (TrnID)
> )
> Create Index TrnDTIndex on TransactionHistory(TrnDT)
> create table PersonTransactionHistory (
> TrnID int,
> PersonID int,
> --extended data pertaining only to "person" transactions goes
> --here. only Person transactions have this
> Primary Key Clustered(TrnID),
> Foreign Key (TrnID) references TransactionHistory (TrnID)
> )
> Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

Given your query, it could be a good idea to have the clustered index
on TrnDT and PersonID instead. The main problem now with the queries
is that SQL Server will have to make a choice between Index Seek +
Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
This is a guessing game that does not always end up the best way.

Of course, you may have other queries that are best off with clustering
on the Pkey, but this does not seem likely. (Insertion may however
benefit from a montonically increasing index. A clustered index on
PersonID may cause fragmentation.)

> 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> well. Then create a foreign key relationship of PersonTransactionHistory
> (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> (PersonID, TrnDT). This seems like it would let SQL Server make
> much more efficient execution plans. However, I am unsure if SQL server
> can leverage the FK on TrnDT to use those new indexes if I give it a query
> like:
> select * from TransactionHistory TH
> inner join PersonTransactionHistory PTH
> on TH.TrnID = PTH.TrnID
> where PTH.PersonID in some criteria
> and TH.TrnDT between some date and some date

Well, take a copy of the database and try it!

(But first try changing the clustered index.)

> 2 - Just coalesce the two tables into one. The original intent was to save
> space by not requiring extra columns about Persons for all rows, many of
> which did not have anything to do with a particular person (for instance a
> contact point going active).

Depends a little on the ration. If the PersonTransactionHistory is 50%
of all rows in the main table, collapsing into one is probably the best.
If it's 5%, I don't think it is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Oct 2004, Erland Sommarskog wrote:

> Given your query, it could be a good idea to have the clustered index
> on TrnDT and PersonID instead. The main problem now with the queries
> is that SQL Server will have to make a choice between Index Seek +
> Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
> This is a guessing game that does not always end up the best way.
> Of course, you may have other queries that are best off with clustering
> on the Pkey, but this does not seem likely. (Insertion may however
> benefit from a montonically increasing index. A clustered index on
> PersonID may cause fragmentation.)

My intuition agrees with you regarding the index in this case. I'm
pretty sure the clustered bookmark scan kills us on many reports. However
I haven't looked with enough depth at the wide variety of queries we use
to know for sure where I should put the clustered index so I'm reserving
judgement for now. I'd also like to study a bit more first so that I
don't replace one hasty decision with another - it might solve ad
individual problem but exacerbate others.

For instance, I think

select * from PersonTransactionHistory PTH
inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
where PTH.PersonID = 12345

would be harmed by moving the TH clustered index from TH.TrnID to
TH.TrnDT, as it would now have to make the same lookup vs scan choice in
order to perform the join. Does that make sound reasonable? And since it's
rare for us to access PTH without the inner join to TH, there are probably
many queries like this.

> > 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> > well. Then create a foreign key relationship of PersonTransactionHistory
> > (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> > indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> > (PersonID, TrnDT). This seems like it would let SQL Server make
> > much more efficient execution plans. However, I am unsure if SQL server
> > can leverage the FK on TrnDT to use those new indexes if I give it a query
> > like:
> > select * from TransactionHistory TH
> > inner join PersonTransactionHistory PTH
> > on TH.TrnID = PTH.TrnID
> > where PTH.PersonID in some criteria
> > and TH.TrnDT between some date and some date
> Well, take a copy of the database and try it!

I appreciate the value of experimentation and normally would do that but
if it didn't work that wouldn't necesarily prove to me that I wasn't
simply doing something wrong like not making the foreign key specific
enough or putting something in my query which made SQL server ignore this
potential valuable relationship. So I was basically wondering if there
were any good docs regarding what types of information SQL Server will and
will no leverage in its choices or whether someone familiar with those
rules had some feedback off the top of their head.

> > 2 - Just coalesce the two tables into one. The original intent was to save
> > space by not requiring extra columns about Persons for all rows, many of
> > which did not have anything to do with a particular person (for instance a
> > contact point going active).
> Depends a little on the ration. If the PersonTransactionHistory is 50%
> of all rows in the main table, collapsing into one is probably the best.
> If it's 5%, I don't think it is.

It's probably between 20% and 40% depending on the particular
installation. It's your rationale that for 50% the space saved is
negligible whereas for 5% is is not? For me it's as more about limiting
the changes to the client software (definitely keeping the tables
separate) vs speeding up queries (possible coalescing) rather than a space
consideration. I did a test once and recall discovering we took up nearly
as much or more space with our indexes than our tables anyway, so
coalescing might make a big space difference anyway. (This amount of index
space suprised me but I'm not sure if there is a good rule of thumb for
how much space indexes should take.)

Rereading the post I probably should have just asked for good table design
references right up front. Any takers?

Thanks for the feedback.

Dave|||Metal Dave (metal@.spam.spam) writes:
> For instance, I think
> select * from PersonTransactionHistory PTH
> inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
> where PTH.PersonID = 12345
> would be harmed by moving the TH clustered index from TH.TrnID to
> TH.TrnDT, as it would now have to make the same lookup vs scan choice in
> order to perform the join. Does that make sound reasonable? And since it's
> rare for us to access PTH without the inner join to TH, there are probably
> many queries like this.

Let's assume for the example that the clustered index in FTH is on PersonID.
Then the join against TH on TrnID will be akin to Index Seek + Bookmark
Lookup, no matter if the index on TrnID is clustered or not. In both
cases you would expect a plan with a Nested Loop join which means that
for each in FTH you look up a row in TH. The only difference if the index
on TrnID is non-clustered, is that you will get a few more reads for
each access. Which indeed is not neglible, since it multiplies with the
number of rows for PersonID.

And just like "SELECT * FROM tbl WHERE nonclusteredcol = @.val" has a
choice between index seek and scan, so have this query. Rather than
nested loop, the optimizer could go for hash or merge join which would
mean a single scan of TH. I would guess that the probability for this is
somewhat higher with a NC index on TrnID.

Of course, you opt to change only FTH, if you like.

> I appreciate the value of experimentation and normally would do that but
> if it didn't work that wouldn't necesarily prove to me that I wasn't
> simply doing something wrong like not making the foreign key specific
> enough or putting something in my query which made SQL server ignore this
> potential valuable relationship. So I was basically wondering if there
> were any good docs regarding what types of information SQL Server will and
> will no leverage in its choices or whether someone familiar with those
> rules had some feedback off the top of their head.

SQL Server does look at constraints, but really how intelligent it is,
I have not dug into. Thus, my encouragement of experimentation.

> It's probably between 20% and 40% depending on the particular
> installation. It's your rationale that for 50% the space saved is
> negligible whereas for 5% is is not?

Actually, I was more thinking in terms of performance, but space and
performance are related. My idea was that with 50%, the space saved is not
worth the extra complexity, and performance may suffer. With 5%, you save a
lot of space, since FTH would be a small table.

Your concern of having to change the client is certainly not one to be
neglected, and if this is costly in development time, I don't think it's
worth it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BEGIN......COMMIT TRANSACTION

I found an issue in our code but I am not sure what the effects are. What
happens if you have a named transaction and the being transaction has a
different name as the commit? Example
begin transaction test
CODE HERE
commit transaction test1
I found a case where there is a typo and the name in the being does not
match the name in the commit. The code in the transaction does commit and n
o
errors are returned. Is the transaction still open at this point?The BOL in 'nested transactions' suggests that name of the COMMIT
TRANSACTION is ignored, so I suppose it shouldn't affect your database.
Peter
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:DB52A984-7136-473A-BCF5-F6E0664E08CD@.microsoft.com...
>I found an issue in our code but I am not sure what the effects are. What
> happens if you have a named transaction and the being transaction has a
> different name as the commit? Example
> begin transaction test
>
> CODE HERE
> commit transaction test1
> I found a case where there is a typo and the name in the being does not
> match the name in the commit. The code in the transaction does commit and
> no
> errors are returned. Is the transaction still open at this point?|||Correct, and this is easy to test:
BEGIN TRAN X
SELECT @.@.TRANCOUNT
COMMIT TRAN Y
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:%236AVWMhQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> The BOL in 'nested transactions' suggests that name of the COMMIT TRANSAC
TION is ignored, so I
> suppose it shouldn't affect your database.
> Peter
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:DB52A984-7136-473A-BCF5-F6E0664E08CD@.microsoft.com...
>

BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

Hi have have two linked SQL Servers and I am trying to get things working
smootly/quickly.

Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?

Basicly, these SPs update a local table and a remote table in the same
transaction. I cant have one table updated and not the other. Please dont
say replicate the tables either as at this time, this is is not an option.

I have for example a number of stored procedures that are based around the
following:
where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkn3j2$2om$1@.sparta.btinternet.com...
> Hi have have two linked SQL Servers and I am trying to get things working
> smootly/quickly.
> Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?
> Basicly, these SPs update a local table and a remote table in the same
> transaction. I cant have one table updated and not the other. Please dont
> say replicate the tables either as at this time, this is is not an option.
> I have for example a number of stored procedures that are based around the
> following:
> where ACSMSM is a remote (linked) SQL Server.
> procedure [psm].ams_Update_VFE
> @.strResult varchar(8) = 'Failure' output,
> @.strErrorDesc varchar(512) = 'SP Not Executed' output,
> @.strVFEID varchar(16),
> @.strDescription varchar(64),
> @.strVFEVirtualRoot varchar(255),
> @.strVFEPhysicalRoot varchar(255),
> @.strAuditPath varchar(255),
> @.strDefaultBranding varchar(16),
> @.strIPAddress varchar(23)
> as
> declare @.strStep varchar(32)
> declare @.trancount int
> Set XACT_ABORT ON
> set @.trancount = @.@.trancount
> set @.strStep = 'Start of Stored Proc'
> if (@.trancount = 0)
> BEGIN TRANSACTION mytran
> else
> save tran mytran
> /* start insert sp code here */
> set @.strStep = 'Write VFE to MSM'
> update
> ACSMSM.msmprim.msm.VFECONFIG
> set
> DESCRIPTION = @.strDescription,
> VFEVIRTUALROOT = @.strVFEVirtualRoot,
> VFEPHYSICALROOT = @.strVFEPhysicalRoot,
> AUDITPATH = @.strAuditPath,
> DEFAULTBRANDING = @.strDefaultBranding,
> IPADDRESS = @.strIPAddress
> where
> VFEID = @.strVFEID;
> set @.strStep = 'Write VFE to PSM'
> update
> ACSPSM.psmprim.psm.VFECONFIG
> set
> DESCRIPTION = @.strDescription,
> VFEVIRTUALROOT = @.strVFEVirtualRoot,
> VFEPHYSICALROOT = @.strVFEPhysicalRoot,
> AUDITPATH = @.strAuditPath,
> DEFAULTBRANDING = @.strDefaultBranding,
> IPADDRESS = @.strIPAddress
> where
> VFEID = @.strVFEID
> /* end insert sp code here */
> if (@.@.error <> 0)
> begin
> rollback tran mytran
> set @.strResult = 'Failure'
> set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
> return -1969
> end
> else
> begin
> set @.strResult = 'Success'
> set @.strErrorDesc = ''
> end
> -- commit tran if we started it
> if (@.trancount = 0)
> commit tran
> return 0

Since you're doing an UPDATE on the remote server, the two are equivalent -
MSSQL will promote the local transaction to a distributed one automatically.
This doesn't necessarily happen for executing stored procedures remotely,
though - in that case you do need to use BEGIN DISTRIBUTED TRAN, or set
'remote proc trans' on for the server, which will make it automatic for
procedure calls also.

Simon

Begin Transaction in Asp.net

Hi All,

Can any one help by giving me the details/difference in using the

Transaction Isolation Levels (read uncommitted,read committed,repeatable read, orserializable)

in asp.net. I just want to know in which case we can use these things in begining a transaction, and will it improve the performance.

thanks in advance

Boo

Here's one article I found by searching Google forsql server transaction isolation levels:http://www.mssqlcity.com/Articles/General/TIL.htm|||

Thanks for the link but i have seen an elobrated content of the same in microsoft.com itself. My doubt is how i can use it in my VB.Net to control the transaction

Thanks

Boo

|||

jbo:

Hi All,

Can any one help by giving me the details/difference in using the

Transaction Isolation Levels (read uncommitted,read committed,repeatable read, orserializable)

in asp.net. I just want to know in which case we can use these things in begining a transaction, and will it improve the performance.

thanks in advance

Boo

The first link covers what you are asking ADO.NET transactions with SAVE POINTs which allow you to run nested transaction blocks, it is very important if you are running more than one because if you get an error transactions without SAVE POINTs all will roll back to number one. The second link download the PDF file and the ZIP file Dusan Petkovic explained SQL Server transactions better than any writer I know. Isolation level serializable is the higest should be left to banks and SQL Server because through lock escalation SQL Server can take control of your isolation level, the others read the PDF file in the second link below. Hope this helps.

http://1.davidhayden.com/blog/dave/archive/2005/10/15/2517.aspx
http://books.mcgraw-hill.com/getbook.php?isbn=0072260939&template

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but don
t
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but dont
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

BEGIN TRANSACTION COMMIT TRANSACTION help

I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')

So table testTable1 has a unique index on it..

Now try to run these statements:

--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION

SELECT * FROM testTable2

..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.|||Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...

So, I have modified the test as follows and it works:

DECLARE @.errorHolder int
SELECT @.errorHolder = 0
--DELETE FROM testTable2
BEGIN TRANSACTION aaa
insert into testTable1(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
insert into testTable2(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
IF @.errorHolder > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SELECT * FROM testTable2

Is there an easier way|||Use
SET XACT_ABORT ON
to automatically roll back a transaction if a run-time error is raised in the TSQL statements.|||thanks kaffenils, that's much easier.|||I prefer to try and catch my errors withg error handling and then I have control over my logic

BUT DDL?|||I prefer to try and catch my errors withg error handling and then I have control over my logic

I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @.@.ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.|||Time consuming? That's your justification?!
Oh dear...|||Time consuming? That's your justification?!
Oh dear...

Did you even read what I wrote? Have I said not to use TRY...CATCH or @.@.ERROR if you need more control of exceptions?
"Oh Dear" :shocked:

All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
IF @.@.ERROR<>0 BEGIN ... END or [GOTO abc
On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.

BEGIN TRANSACTION

have an application (c# net 1.1) that attaches to a MSDE 2000(fully patched)
machine the application runs for some time, but eventually enters a
begintransaction. it does not error, but it does not return from the call
either it just hangs.
have connected from another machine with enterprise manager and looked for
blocking processes etc but there are none.
It might run for hours, or a few minutes before the hang occurs
basically
write a log event
Begin transaction
write a log event.
all the code is in a try catch block, with logging, almost every line in the
procedure is now wrapped in a write a log event. it is hanging on the begin
transaction system, the connection is good.
hang in this case means forever, have to kill the application.
There are no other transactions running, and enterprise manager from a
remote machine can access the data when the application is hung.
Need some ideas as to what else to look for.
hi,
JR wrote:
> have an application (c# net 1.1) that attaches to a MSDE 2000(fully
> patched) machine the application runs for some time, but eventually
> enters a begintransaction. it does not error, but it does not return
> from the call either it just hangs.
> have connected from another machine with enterprise manager and
> looked for blocking processes etc but there are none.
> It might run for hours, or a few minutes before the hang occurs
> basically
> write a log event
> Begin transaction
> write a log event.
> all the code is in a try catch block, with logging, almost every line
> in the procedure is now wrapped in a write a log event. it is hanging
> on the begin transaction system, the connection is good.
> hang in this case means forever, have to kill the application.
> There are no other transactions running, and enterprise manager from a
> remote machine can access the data when the application is hung.
> Need some ideas as to what else to look for.
try having a look at
http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
if you can find some info about eventual pending transactions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea for your response
I have run DBCC OPENTRAN and no results are returned
Well actually the text "No active open transactions"
the call to begintransaction just locks and never returns
I really don't see anything blocking the begintransaction from working
Thanks
JR
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47b3h7FemenlU1@.individual.net...
> hi,
> JR wrote:
> try having a look at
> http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
> if you can find some info about eventual pending transactions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> Thanks Andrea for your response
> I have run DBCC OPENTRAN and no results are returned
> Well actually the text "No active open transactions"
> the call to begintransaction just locks and never returns
> I really don't see anything blocking the begintransaction from working
yep, it just seems non transaction at all has been opend and your
application just gets stuck somewhere..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||yep, stuck in the .begintransaction so now what ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47d6h2FerknmU1@.individual.net...
> hi,
> JR wrote:
> yep, it just seems non transaction at all has been opend and your
> application just gets stuck somewhere..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> yep, stuck in the .begintransaction so now what ?
perhaps in .net hierarchy in the language syntax..
if you can perform the included T-SQL command text without problem, I can
suppose the problem is elsewhere :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You might want to turn on profiler to see if the begin transaction statement
is received and completes in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47dj1jFeh0ueU1@.individual.net...
> hi,
> JR wrote:
> perhaps in .net hierarchy in the language syntax..
> if you can perform the included T-SQL command text without problem, I can
> suppose the problem is elsewhere :-(
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger
this is a good idea,
in the interim, have commented out the transaction altogether and
the code is running perfectly when it is not in a transaction.
Soon as the code uses a transaction it runs for a while and then hangs.
JR
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ewr$5IGRGHA.5108@.TK2MSFTNGP09.phx.gbl...
> You might want to turn on profiler to see if the begin transaction
> statement is received and completes in the database.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:47dj1jFeh0ueU1@.individual.net...
>

Begin Trans

If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.

TomWell...there's a lot of reading you can do in books online...BUT, just as a good general coding practice you should...

I like to follow this template...I'd say most of my sprocs are this way...like 90%

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Begin tran/commit

Hi,
Is it good to have two transaction i.e two begin/commit in one SP.
like
beging
update..
commit
begin
update...
commit.
any performance improvements in any case or otherwise..If those two updates can be commited or rolled back independently from one
another, then your solution is OK.
But if they should be treated as a single atomic unit of work, you should
either enclose them into a single transaction (be it each in its own nested
trasaction or not), and make sure they are committed (or rolled back) as a
single unit of work (i.e. if one needs to be rolled back, the other either
never begins or is rolled back).
Other than that, based on your post, I see no problem. Unless this is your
entire procedure, in which case I see a gazzillion of them. :)
ML|||hi Sunny
This depends on your requirement. You can have 2 transactions in a SP.
If u have 2 different sets of queries, u can group them into two different
queries
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sunny" wrote:

> Hi,
> Is it good to have two transaction i.e two begin/commit in one SP.
> like
> beging
> update..
> commit
> begin
> update...
> commit.
> any performance improvements in any case or otherwise..

Begin Tran / Commit Tran

Any reason why you would need to wrap a transaction around a single update?
BEGIN TRANSACTION
UPDATE ThisTable
SET blah blah blah
WHERE foo = @.bar
COMMIT TRANSACTION
?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldNo need. The only reason I can think of if you were to name the transaction.
However, naming
transaction has little benefit.
I think you can see the transaction names as they are rolled forward and rol
led back during
automatic recovery in the errorlog (at least you used to be able to do that)
. That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx
.gbl...
> Any reason why you would need to wrap a transaction around a single update
?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> No need. The only reason I can think of if you were to name the
> transaction. However, naming transaction has little benefit.
> I think you can see the transaction names as they are rolled forward and
> rolled back during automatic recovery in the errorlog (at least you used
> to be able to do that). That is about the only reason I can think of.
> Without a name = waste of precious keyboard keys.
Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.
You may have a structure something like:
CREATE PROCEDURE procName ...
BEGIN
..
SET @._TRANCOUNT = @.@.TRANCOUNT
IF @._TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
..
IF @._TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName
RETURN 0 -- indicate success
ERROR:
IF @._TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @.@.TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName
IF @.RC > 0 RETURN @.RC -- return error from called sp
IF @._ERROR > 0 RETURN @._ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Any reason why you would need to wrap a transaction around a single
update?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>

Begin Tran ...... Commit Tran

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Hi ,
Go through the sql online books.|||

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Refer
http://msdn2.microsoft.com/en-us/li...83(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://www.mssqlcity.com/Articles/General/TIL.htm|||

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

simple to roll back the qry process done

begin and end transaction and transaction log

Hello everyone,
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?

Begin trans T1

Update sometable
Set random_row = 'blah'

End trans T1

Thanks!Kruton (wmlyerly@.gmail.com) writes:

Quote:

Originally Posted by

This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
>
Begin trans T1
>
Update sometable
Set random_row = 'blah'
>
End trans T1

Why would you truncate the transaction log in the first place?

If you run with full recovery and want to be table to restore to a point
in time, the you should backup your transaction log regularly.

If you don't care about the point-in-time restores but are content with
restoring from a full backup in case of a failure, you should set the
database in simple recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erlang,
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.

Thanks.

On Dec 12, 2:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Kruton (wmlye...@.gmail.com) writes:

Quote:

Originally Posted by

This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?

>

Quote:

Originally Posted by

Begin trans T1

>

Quote:

Originally Posted by

Update sometable
Set random_row = 'blah'

>

Quote:

Originally Posted by

End trans T1

>
Why would you truncate the transaction log in the first place?
>
If you run with full recovery and want to be table to restore to a point
in time, the you should backup your transaction log regularly.
>
If you don't care about the point-in-time restores but are content with
restoring from a full backup in case of a failure, you should set the
database in simple recovery.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

|||"Kruton" <wmlyerly@.gmail.comwrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@.o42g2000hsc.googlegroups.com...

Quote:

Originally Posted by

Hi Erlang,
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.

Then your DBA needs to set the DBA to simple recovery.

Quote:

Originally Posted by

>
Thanks.
>
On Dec 12, 2:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>Kruton (wmlye...@.gmail.com) writes:

Quote:

Originally Posted by

This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?

>>

Quote:

Originally Posted by

Begin trans T1

>>

Quote:

Originally Posted by

Update sometable
Set random_row = 'blah'

>>

Quote:

Originally Posted by

End trans T1

>>
>Why would you truncate the transaction log in the first place?
>>
>If you run with full recovery and want to be table to restore to a point
>in time, the you should backup your transaction log regularly.
>>
>If you don't care about the point-in-time restores but are content with
>restoring from a full backup in case of a failure, you should set the
>database in simple recovery.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide
>quoted text -
>>
>- Show quoted text -

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Kruton (wmlyerly@.gmail.com) writes:

Quote:

Originally Posted by

This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.

Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
This sounds like it could be it. I will give it a try. Thanks

On Dec 13, 12:21 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Kruton (wmlye...@.gmail.com) writes:

Quote:

Originally Posted by

This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.

>
Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Begin and Commit Transaction statements in a trigger

I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
Paddy
Paddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>
|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
Thanks, Liliya
|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
. I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.
|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.
|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go
|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
|||RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF