Posted by Erland Sommarskog on 10/02/08 11:48

pb648174 (google@webpaul.net) writes:
> We shrink the transaction log every two hours without any noticeable
> performance hit, but our total database size is less than 500 MB. I
> have played with the simple logging one time and had problems with it,
> so we have been sticking with our two hour shrink process (added to our
> two hour backup process) which works fine for now.
>
> DBCC SHRINKFILE(DBName_log, 1)
> BACKUP LOG DBName WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBName_log, 1)

This appears completely pointless to me. If you have full recovery,
you are losing all the benefits by truncating the transaction log
every two hours. If you have simple recovery, there should not be
any need to shrink the file, unless there has been any extraordinary
actions going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Question on Transaction Log Backups
• Prev in forum: Re: Timeout errors
• Thread view: Re: Question on Transaction Log Backups

[Reply to this message]