large transaction logs

Question

Post reply

large transaction logs

Mark Hemelrijk

Old Hand

Points: 394
More actions
August 20, 2007 at 3:07 am

#121246

Sinces we're using SQL Server 2005 all our databases are switched to full recovery model. I noticed that some transaction logs are rather large (600Mb). I think this is not right as we back-up these files every hour using a maintenance plan. Can somebody tell me how I can reduce the size of these transaction logs?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 1

Oops, this message should be in administrating SQL Server 2005

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

just open a new thread in sql2005

Which recovery model did you use before sql2005 ?

Keep in mind full recovery logs everything ! (also indexrebuilds,...)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 3

In SQL Server 2000 we used the simple recovery model.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

with simple recovery, a transcation is marked to be overwritable (in the transaction log file) once it is committed or rolled back.

You need to make log-backups to keep your transaction logs from growing if you have full or bulk recovery model enabled.

Check "Overview of the Recovery Models" in BOL.

If you have a "exceptional" action that did cause your db-log-file(s) to grow way over its normal proportion you can shrink it.

Fullbackup - shrinkfile (until succeeded) - fullbackup

Check bol or http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD

or http://www.support.microsoft.com/kb/256650

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 5

@ Moderator

Can you please move this thread to the right section (MS SQL Server 2005)? Thnx

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

This has been moved.

The logs being backed up at 600MB every hour is interesting. Is this an extremely busy system? Can you give us an idea of db size, full backup size, and then the log sizes for the log after a full and then maybe a few hours after?

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

If ur recovery model is SIMPLE then u cant take transaction log backups.Try dbcc shrinkfile(log_name,size)

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 8

Check to see if any jobs are hanging and/or if transaction log backup is really working (i.e. not failing).

We once had a backup job hang for 2 days - transaction log backup kept failing, and the transaction log was growing, until it almost filled up the hard disk.

Also, are you doing any index defragmentation?

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 9

Thanks everyone. I'll post detailed database information & shrink results as soon as possible.