Transaction log file size - does it do any harm?

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?

Re: Transaction log file size - does it do any harm?

"Trevor Best" 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

Re: Transaction log file size - does it do any harm?

On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
wrote:
> "Trevor Best" 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?

I think it has 18 months worth of transactions in it. (I'm not their
dba :-)

> 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.

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.

Re: Transaction log file size - does it do any harm?

"Trevor Best" wrote in message
news:1179756303.063662.221950@y2g2000prf.googlegroups.com...
> On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
> wrote:
>> "Trevor Best" 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?
>
> I think it has 18 months worth of transactions in it. (I'm not their
> dba :-)
>

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


>> 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.
>
> 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.
>

I'd somehow doubt it....

Re: Transaction log file size - does it do any harm?

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