truncate the transaction log

[DirtyB]

is there a way to truncate the transaction log from transact code?

 

[grahamm]

Using TSQL against a database called foo:

exec sp_dboption foo, 'trunc. log on chkpt', 'true'
checkpoint

This will truncate the inactive part of the transaction log once the checkpoint completes. However understand what you're doing before altering dboptions -- look in BOL under sp_dboption.

gm

 

[tlbroadbent]

You should be aware that truncating the log invalidates future transaction log backups until a full or differential backup is done.

In SQL 7 or 2000 use...

Backup Log With Truncate_only

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DirtyB]

Thanks guys...I just have a stored proc here that runs a TON of updates, and it always crashes because my transaction log gets full, so I wanted to be able to truncate it, periodically throughout the stored proc.

Thanks

Jason