Posted by Greg D. Moore \(Strider\) on 05/21/07 11:36

"Trevor Best" <googlegroups@besty.org.uk> wrote in message
news:1179743114.957187.301860@b40g2000prd.googlegroups.com...
>I have a client with 150MB database, the transaction log file is
> nearly 23GB. It's a PITA for me to backup his data and restore it on
> my server as it takes about 30 minutes to restore as it re-creates a
> 23GB file with no transactions in it :-\
>

Why is it that large?


> As well as the space taken up by it, does it do any harm being that
> size? e.g. does it decrease performance?
>

Most likely not.


> While it's there, considering the database is backed up daily, does it
> actually do any good?

"it depends"

>
> I know how to truncate and shrink logs, is it advisable / safe /
> worthwhile to do this on a regular basis?

No.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I'd find out first why it's a 23GB transaction log file. If it's a fluke,
then yes, a single shrink to a more reasonable size is probably ok. My guess
is at some point they simply were NOT doing transaction log backups and as a
result it just kept growing. So they either instituted transaction log
backups (good) or put the database into "SIMPLE" recovery mode (potentially
bad) and just left the log file the size it was.


>



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Back to original message]