Ever growing Transaction logs



  • 1. qualifying Stored Procedure with owner. Why is it important/
    I've been reading some posts that indicate that it is best to invoke a Stored Procedure with the owner name. Example exec dbo.MyStoredProc instead of exec MyStoredProc I never really understood why this makes a difference. I would appreciate it if someone could shed some light on this Thanks in advance
  • 2. Optimized & fastest way of taking backup & restoring database through C# code
    BACKUP/RESTORE,sp_detach_db, sp_attach_db system stored procedures.For details please refer to the BOL "Pankaj Jain" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ... > Hi , > > Can any one tell me the Optimized & fastest way of taking backup & > restoring my database that is in SQL Server through C# code. > Currently I am using SQLDMO .Is there any Better approach of achiving > the same? > > Regards > Pankaj Jain >
  • 3. 'Space Available' Is 0, but I can make records, tables...
    Hi, I have something rather strange, we have an SQL 2000 standard running and on one of the database (the MS CRM3.0) database, the 'Space Available' property says 0.00 MB. When I add a table or a column in a table, this suceeds without any error. But when we do this in CRM this fails, we think that the property of the database Space Available is checked before inserting new values. Why is this property 0,00 MB? We have set the autoincrement properties and unrestricted growth; the physical disc has enough space available. Does anyone have an idea how we can fix this or look for the possible cause? Thanks,
  • 4. Undo database import
    Hi, anyone knows if there's a way (in SQL 2005 + Windows Server 2003) to recover a database contents prior to an import operation ? Backups are not up to date so don't help. Thanks. Massimo.

Ever growing Transaction logs

Postby dave » Thu, 25 Dec 2003 02:41:01 GMT

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 

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.  

Windows 2000 SP4
SQL 2000 SP 3
IBM server - four processors
three 400GB data partitions located on an IBM Shark SAN

Re: Ever growing Transaction logs

Postby Andrew J. Kelly » Thu, 25 Dec 2003 03:59:11 GMT


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


Andrew J. Kelly
SQL Server MVP

Similar Threads:

1.Ever growing transaction log, using simple recovery?

I've read a lot of posts on growing transaction logs. Most suggest that
setting it to Simple Recovery ensures that is automatically shrinks.
Ours has been running Simple from the start. It's a database that is
mostly read-only, some tables are filled using replication.

Our production database is currently 160Gb, with an ever growing
transactionlog which has now grown to 130Gb. The disks can handle the
space requirement, but we're facing an ever growing increase in backup
time, and heaven forbid, restore time.

Whatever we tried, short of a suggestion of detaching the database and
reattaching, nothing seems to help.

We're running this on SQL 2005 SP1.

Any ideas, suggestions?


2.Investigating what makes my log file grow and grow and grow

I need to learn the steps to investigate which 
transactions are not getting replicated, thus causing my 
log file to grow.  I periodically check the dbs I 
administer and wait until after hours to force 
replacation done using sp_ReplDone, followed by a backup 
and shrink, for those dbs with excessively large log 
files.  I only have this problem on the server that acts 
as distributor and subscriber.

I even set up jobs that do the following, in order (by 
start time):
1)exec sp_ReplDone @xactid = NULL, @xact_segno = NULL, 
@numtrans = 0, @time = 0, @reset = 1
2)Backup transaction log (part of maintenance plan)
3)Backup db (part of maintenance plan)
4)DBCC Shrinkfile

I have these jobs spread out every 1/2 hour to give them 
plenty of time to complete before the next one begins.  
Yet the log file doesn't shrink.

When I run DBCC OPENTRAN on the db, I get the following:
Transaction information for database 'Database1'.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (570647:21697:1)
DBCC execution completed.

So, my two requests are:
1) why doesn't my shrink procedure work? (If I do these 
steps by hand I can shrink the log file down to 1MB)
2) how do I determine what's causing the hangup that 
starts the log file to grow.


3.DTS Copy Database Task and ever increasing Transaction Log

I am fairly new to DB management.  One of the things I have to work with is a 
DTS Package that simply copies a database.  One thing that I have noticed is 
that every time this runs the transaction log increases and doesn't seem to 
clean itself out even when I shrink the database.  Is there something missing 
in the DTS that should drop the transaction log?  The Transaction Log is 
currently 103 GB  and the data is only .5 GB.  Any insight will be greatly 


4.Ever increasing transaction log size

Hi guys

I have a database thata have a roughly 1 Gb datafile.
The issue I seem to be facing is the fact that the transaction log keeps
Currently the transaction log is just short of 5 Gb however the backup of
the transaction log is always below 1 Gb. Every morning when I come in I see
the same pattern the transaction log size is increased by a few Mb however
the backup file of the log is only around 900 Mb.

Can anyone explain to me how and why the log file keeps growing?


5.Transaction log keeps growing


We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the database. Nevertheless
the size has increased. I would imagine that a transaction log can be
eliminated if we stop the database. Can that be done? Is there a way to
completely wipe off the transaction log?


6. Transaction log databseNAME_log.ldf keeps growing

7. Transaction Log files not growing

8. Virtual SQL Server Transaction Log Auto-Grow problem.



Who is online

Users browsing this forum: No registered users and 77 guest