Transaction log file size - does it do any harm?

[Trevor Best]

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?

[Greg D. Moore \(Strider\)]

"Trevor Best" <go**********@besty.org.ukwrote in message
news:11**********************@b40g2000prd.googlegr oups.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

[Trevor Best]

On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
<[e-mail]:

"Trevor Best" <[e-mail] in message

news:11**********************@b40g2000prd.googlegr oups.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.

[Greg D. Moore \(Strider\)]

"Trevor Best" <go**********@besty.org.ukwrote in message
news:11**********************@y2g2000prf.googlegro ups.com...

On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
<[e-mail]:

>"Trevor Best" <[e-mail] in message

news:11**********************@b40g2000prd.googleg roups.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....

[barlowedward]

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