Hi,

I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance?? We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).

We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.

After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.

Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.

Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!

Thanks,
Chris

It is unlikely that you have enough information to decide what action you should take. If you are not already collecting stats on Physical Disk Average Disk Write Queue Length, Memory Available MBytes, [SQL Instance] Cache Manager Buffer Hit Ratio, [SQL Instance] Memory Manager Target Server Memory(kb) and [SQL Instance] Memory Manager Target Server Memory(kb). This perfmon counters will give you a better picture of what is happening

You didn't mention if your data/logs/backup disks were on the same physical disks. If the logwriter has to compete with data retieval and updates this will result in some of the symptons that you are experiencing