Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Truncate TRN Log every day good idea/bad idea?

Author  Topic 

uberman
Posting Yak Master

159 Posts
Posted - 2009-07-24 : 04:36:02
It has been suggested to me that I should be truncating the transaction log every night after a full backup to free disk space.

I know this a bad idea and am trying to back it up; I have tried googling for the answer without much success, does anyone have a handy "authoritive" link as to why truncating your transaction logs everyday is ... suboptimal?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-07-24 : 05:03:47
Definitely a bad idea.

If you're in full of bulk-logged recovery, you should be backing up the transaction log regularly to allow point-in-time recovery. If you don't care about point-in-time recovery, set the DB into simple recovery model and the log will truncate itself regularly.

As for some links:
[url]http://www.sqlservercentral.com/articles/64582/[/url] (not necessarily authoritative, it's mine)
http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
http://technet.microsoft.com/en-us/magazine/dd776512.aspx

--
Gail Shaw
SQL Server MVP
Go to Top of Page

uberman
Posting Yak Master

159 Posts
Posted - 2009-07-24 : 05:04:49
Thank you very much for the reply, exactly what I needed.

Cheers
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site