ADO apps filling up tran logs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ADO apps filling up tran logs

More than once I have come accross a problem where ADO based applications seem to create very large transaction log files. The server is SQL Server 2000. The recovery model is full – of course. Transaction log backups don’t seem to clear down the logs as I would have expected. The latest incarnation of this problem shows itself when a replication job takes place to transfer data from one database to another database on another server. The target database’s tran log grows many times bigger than the data file and that’s the way it stays unless I manually sort it out. As I understand it, ADO is in ‘Autocommit’ mode – so every transaction should be automatically committed. Therefore, old transactions should be automatically marked as inactive? If so, why does the transaction log keep growing? Shouldn’t regular transaction log backups clear down the transaction log? Clive
The setting will not have any affect on the transaction log size. The transactions that are commited must be marked inactive. If the transactions had not been commited, the same won’t reflect in the daatbase once the application has finished execution. So I believe the size of the log file is not reduced may be because there is an active transaction at the end of the file. Did you check the %age log space used after the transaction log backup? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Run DBCC SQLPERF(LOGSPACE) as referred to see the log %age. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Yes, I checked the %age of log space used – it’s high. Hence log file growths. If all transactions are via ADO and all ADO transactions are autocommit by default, there should not be any open transactions hanging around? I checked for open transactions greater than a certain age and there weren’t any. I thought the same as you as long running transactions seemed to be the only logical explanation for a continiously growing log file. However, there must be another reasonable explanation. Clive
What is the size of data affected during the operation? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Small… Something like 75Mb. Log file can grow to 3Gb! Not sure what the ADO app is up to but, as I understand it, all transactions are by default ‘auto commit’ from ADO. So, as I said before, there should not be any long running transactions left hanging around. In the normal course of events, what events clear our the inactive portion of the transaction log if it is in Full Recovery mode in SQL Server 2000?
Clive
]]>