Karrasko's Co. (10/31/2008)
What do you think about this method?
I think it won't do what you think it will. If you truncate the transaction log then you can neither take a tran log backup nor do any type of point-in-time restore afterwards until another full or diff backup is run.
If your DB failed and you tried to run a log backup, you would get this error (SQL 2005)Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
On SQL 2000, it's worse. The log backup will succeed, but it will not be usable. The truncate throws away log records. In order to restore a log backup, the log chain must be intact. Because the log records are missing, the log chain is not intact.
If you have any form of disaster with the backups run like that, you will be loosing up to a day's data. If that's acceptable, set the DB to simple recovery, because that's essentially what you're in now. If not, set up regular log backups.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass