SQL Server 2000, large transaction log, almost empty, performance issue?

Question

For a company that I have been helping troubleshoot their database.

In SQL Server 2000, database is about 120 gig.

Something caused the transaction log to grow MUCH larger than normal to over 100 gig, some hung transaction that didn't commit or roll back for a few days. That has been resolved and it now stays around 1% full or less, due to its hourly transaction log backups.

It IS my understanding that a GROWING transaction log file size can cause performance issues.

But what I am a little paranoid about is the size. Although mainly empty, MIGHT it be having a negative effect on performance? But I haven't found any documentation that suggests this is true.

I did find this link: http://www.bigresource.com/MS_SQL-Large-Transaction-Log-dramatically-Slows-down-processing-any-idea-why--2ahzP5wK.html but in this post I can't tell if their log was full or empty, and there is not any replies to the post in this link.

So I am guessing it is not a problem, anyone know for sure?

Nick Kavadias · Accepted Answer · 2010-05-31T15:26:02.677

2

What you'll probably find is that because the tlog has grown to such a large size on its own because autogrow was turned on, you'll have a lot of virtual log files. This means the transaction log itself is fragmented and this isn't a good thing.
Here's some information on how you can check how many vlog files the database has.
Here's a blog on why lots of virtual log files are bad & what you need to do about it.

edited May 31 '10 at 15:26

answered May 28 '10 at 14:43

Nick Kavadias

10,758
7
36
47

Thankyou. I can now suggest to them to shrink their transaction log files with these links to use to justify it. – Mafu Josh Jun 01 '10 at 14:10
I'll upvote later when I have the reputation to be allowed to. – Mafu Josh Jun 01 '10 at 14:12

score 1 · Answer 2 · answered May 28 '10 at 14:58

When you backup, SQL Server will truncate your log files and create an initial log file. In your situation it sounds like you should greatly reduce your log file. Try to determine how much data is written to your log between your backup windows and then may double or triple the amount. Set that as your initial log file size. This will help prevent fragmentation performance issues.

Growning your log file by 1MB increments when your average transaction is 2MB is going to be very bad for performance. Growing 400MB twice a day probably won't make a noticable difference in performance.

I highly recommend reading this article's section on Data and Log file management. It will help you make better educated decisions about your issue.

Thankyou, your linked article was helpful, especially the part on virtual log files. I selected the other answer because they answered first. — Mafu Josh, Jun 01 '10 at 14:11
I'll upvote later when I have the reputation to be allowed to. — Mafu Josh, Jun 01 '10 at 14:13

SQL Server 2000, large transaction log, almost empty, performance issue?

2 Answers2