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
Ever growing Transaction logs
2 posts
• Page 1 of 1
- 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.
Next
Ever growing Transaction logs
by dave » Thu, 25 Dec 2003 02:41:01 GMT
Re: Ever growing Transaction logs
by Andrew J. Kelly » Thu, 25 Dec 2003 03:59:11 GMT
Dave, 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
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? Ren
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.
Roger.
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 appreciated. Rob
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 growing. 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? Regards Jonas
5.Transaction log keeps growing
Hi, 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? Thanks, Yash
6. Transaction log databseNAME_log.ldf keeps growing
2 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 77 guest