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 :-\

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

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

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

"Trevor Best" <googlegroups (AT) besty (DOT) org.uk> wrote

Why is it that large?


Most likely not.


"it depends"

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

On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
I think it has 18 months worth of transactions in it. (I'm not their
dba :-)

I did set up backup of db and logs and maintenance plans on their
server when it first installed (Aug 05), I just checked and Agent
isn't running so I guess he normal maintenance plan hasn't run. I hope
they backup the database some other way :-\ Thanks for your comments.

"Trevor Best" <googlegroups (AT) besty (DOT) org.uk> wrote

So doesn't sound like it has NO transactions in it.


I'd somehow doubt it....

If your tran log gets that big you should decide if you care about
tran logs

either

set recovery mode to simple, dump tran with nolog, shrink the database
log file, and take a backup

-or-

set up incrementals using your maintenance plan

-or-

both

if you need point in time recovery, i recommend doing both. Eliminate
the old junk and redump your
database after setting up a plan that dumps tran logs.

Ed