Ever growing Transaction logs

We are experiencing an ongoing transaction log problem
that is driving us crazy. I would appreciate any help you
can provide.
We are currently running an application from a company
called Aelita that collects event log data from our
servers and stores them in an SQL 200 database on a
Windows 2000 server. Our database is currently 160 GB in
size with approximately 140 million records. The system
only has five users who during the day only generate
reports on security and incidents from the event logs in
the database. They can not manipulat the data. They have
been told to stay out of the database until further
notice.
Here's the problem: the transaction logs grow
continuously until they get over 300GB in size and fill up
the partition they are on. There are no users on the
system, no data being collected and no reports being
generated. In theory, there should be no transactions
being porocessed except for any built in SQL processes
that are running. I've run performance monitor against
that database and can see that we are averaging between
300 and 400 transactions per second on the database during
the day. These transactions are filling up the transaction
logs everyday. I can't figure out what is running and
generating the transaction logs.
I've run the profiler in EM, but can't seem to get good
data out of it. I see a number of begin/commit
transactions associated with an OBjectName "Ghost", but I
have no idea what that is or if that is even the problem.
I can't tell which process is running out of control and
causing the problem. The problem continues after shutting
down SQL or rebooting the server. I need to figure out
what is running and stop it.
Because of on going problems, we have shut down the Aelita
application from collecting data. Normally the data is
collected during the night. We haven't collected data for
a couple of weeks while we attempt to solve our problem.
config:
Windows 2000 SP4
SQL 2000 SP 3
IBM server - four processors
4 GB RAM
three 400GB data partitions located on an IBM Shark SANDave,
Take a look to see if you have any scheduled jobs running that may be
causing the transactions such as a reindexing job etc. You should be able
to tell thru sp_who2 what spid is running and then use DBCC INPUTBUFFER to
see what it is doing. Profiler should be able to tell as well but I suspect
you don't have the right event classes selected. Chec sp:completed, batch
completed and rpc completed to get a start. If your not doing regular log
backups you may want to think about setting it to Simple recovery mode as
well.
--
Andrew J. Kelly
SQL Server MVP
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:017701c3c97b$ee765a40$a101280a@.phx.gbl...
> We are experiencing an ongoing transaction log problem
> that is driving us crazy. I would appreciate any help you
> can provide.
> We are currently running an application from a company
> called Aelita that collects event log data from our
> servers and stores them in an SQL 200 database on a
> Windows 2000 server. Our database is currently 160 GB in
> size with approximately 140 million records. The system
> only has five users who during the day only generate
> reports on security and incidents from the event logs in
> the database. They can not manipulat the data. They have
> been told to stay out of the database until further
> notice.
> Here's the problem: the transaction logs grow
> continuously until they get over 300GB in size and fill up
> the partition they are on. There are no users on the
> system, no data being collected and no reports being
> generated. In theory, there should be no transactions
> being porocessed except for any built in SQL processes
> that are running. I've run performance monitor against
> that database and can see that we are averaging between
> 300 and 400 transactions per second on the database during
> the day. These transactions are filling up the transaction
> logs everyday. I can't figure out what is running and
> generating the transaction logs.
> I've run the profiler in EM, but can't seem to get good
> data out of it. I see a number of begin/commit
> transactions associated with an OBjectName "Ghost", but I
> have no idea what that is or if that is even the problem.
> I can't tell which process is running out of control and
> causing the problem. The problem continues after shutting
> down SQL or rebooting the server. I need to figure out
> what is running and stop it.
> Because of on going problems, we have shut down the Aelita
> application from collecting data. Normally the data is
> collected during the night. We haven't collected data for
> a couple of weeks while we attempt to solve our problem.
> config:
> Windows 2000 SP4
> SQL 2000 SP 3
> IBM server - four processors
> 4 GB RAM
> three 400GB data partitions located on an IBM Shark SAN
>