Transaction Log Issues

Hi folks,

Has anyone else seen a problem with the transaction log on SQL Server 2000 growing exponentially for a 4.1 SP1 WorkSite MP DB? I've got a system deployed for a client that has all of about ten active users and one workspace at the moment, and the transaction log keeps growing to GBs in size. In fact, after it's last truncation, it grew to 3GB in size in less than a week. When we first noticed the issue, the log had grown to 45GB(!), so now we're keeping close eye on it. This system is -not- under heavy use, but the SQL Server instance is being used by other apps (MRO's MAXIMO, specifically).

Anyone seen (and solved, I hope) similar behavior?

-John

Find more posts tagged with

Comments

dbguy

This is related to the recovery model and maintenance procedures your client is using. Take a look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;873235

It should be enough to solve your problem.

johnguilfoyle

Yeah, we know the DBs need regular maintenance -- but is is normal for lightly used WSMP databases to have the transaction logs grow so quickly?

Thanks for the link.

-John

dbguy

Sorry I misunderstood your question.

Anyway, yes, MP can make your log files grow fast. The server and any additional back end services (indexer, notification, etc) regularly fire housekeeping transactions to the DB, regardless of usage load. If you are using the web app, it will also fire some transactions.

johnguilfoyle

Aha, I thought as much. But I'm still wondering about the sheer volume of those transactions. In people's experience, do WorkSite MP application transaction logs grow *that* fast? From alloted 100MB to 2.8GB in about four days, very light use on the system? We never saw this kind of volume in dev or test environments.

Thanks for the info so far, dbguy.

-John

dbguy

Sorry, I don't have an answer for that. Last time I saw this problem was a few years ago and I don't remember the growth ratio. I guess support is taking eventual calls on this issue and taking care of it.

example.rptdesign

JTNeville

My users work across hundreds of workspaces and check in and out documents all day long so I have written a script to run the backup then clear whitespace and truncate the t-logs nightly because of their enormous growth (the MS gui tools don't do a good job on this). It has been this way for us since the 3.1 days. hth.

johnguilfoyle

Thanks for the reply, JT.

I've sicced our DBA on this and he's done more or less the same thing. He's concerned about the rate of growth (close to 1GB/day), though, and that it might be a sign of a failing nested query or something. It's troubling to me since I've got WSMP installed in a variety of places, and it's only this one install where t-log growth is out of control. The scripts are taking care of the problem now, but I need to dig a little deeper (perhaps using the SQL Profiler) and try to figure out what's causing the runaway growth.

-John