SQL Server Transaction Log Truncating/Shrinking

I have seen some stored procedures that wrap everything in a transaction.

begin transaction
    update
        table
    set
        c开发者_C百科olumn c = (column a * column b)
commit

Is this the right way to go about managing the transaction log? I've tried this with some stored procedures but it looks like the log just gets out of hand. What's the best way to manage the transaction log when running a stored procedure that has multiple update/insert/alter statements in it?

Any help is greatly appreciated.

A SQL transaction is logged regardless if the query is implicitly wrapped in a transaction statement or recovery model. Depending on the recovery model the transaction will either remain in the log (full recovery mode) or be truncated (simple recovery mode). Regardless of the mode truncating the log does not shrink the file size.

Managing the log is not something that is typically handled in SQL queries within you application, rather a DBA task.

Transactions are stored to allow for recovery in the case of failure. Where you can step through transactions since your last backup to recreate the data at that point in time. Simple recovery mode does not allow this as the transaction is truncated immediately upon successful execution (COMMIT).

The log is typically truncated and shrunk upon backup. You can create SQL maintenance jobs to manage this.

The log is not directly related to how many explicit TRANSACTIONs you have in your query. It's related to your recovery model. The explicit TRANSACTION statements just use the log to rollback or commit upon request.

Also FYI your code would throw an error, since you have a BEGIN TRANSACTION without a COMMIT or ROLLBACK.

You dont need to wrap this in a transaction. It is running one statement so it will either succeed or fail.

If you were updating a second table and needed both to succeed to keep the data consistent then you might want to consider this approach.

However.. The size of the transaction log file will grow until it is backed up. If your database is small you may wish to set the database recovery option to simple and just back up your whole database every night, this will also truncate the transaction log file.