By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,376 Members | 1,566 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,376 IT Pros & Developers. It's quick & easy.

Transaction log file size - does it do any harm?

P: n/a
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?

May 21 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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
May 21 '07 #2

P: n/a
On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@greenms.comwrote:
"Trevor Best" <googlegro...@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?
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.

May 21 '07 #3

P: n/a
"Trevor Best" <go**********@besty.org.ukwrote in message
news:11**********************@y2g2000prf.googlegro ups.com...
On May 21, 12:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@greenms.comwrote:
>"Trevor Best" <googlegro...@besty.org.ukwrote 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....

May 21 '07 #4

P: n/a
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

May 22 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.