Transactions in stored procedures

[Guest_imported]

I have simple stored procedures which have a single update or insert statements. In some cases I have a select followed by insert or update.
Is it necessary to use begin tran & commit tran in these cases ?

Is there a overhead to using begin tran & commit ?
Thanks for your time

 

[Tim1]

Hiya,

It is not strictly necessary to use BEGIN and COMMIT TRANSACTION commands for what you are doing, and stored procedures will do an implicit COMMIT when they complete.

However, it is always a good idea to commit as it clears down the transaction log of any data, and it means that should the server hit a problem, your changes are not lost.

Overhead time is minimal in using these, and more than made up in the event of server problems.

Tim

 

[RickCole]

Guys

Please be aware a commit transaction does not clear down the transaction log. The only way the transaction log is cleared down is by the following methods:

1) If you have the trunc. log on chkpt option set.

2) Using the BACKUP LOG SQL command with appropriate parameters.

Both these methods will remove the inactive portion of the transaction log and must be used as part of your database maintenance strategy.

Rick.