December 27, 2007 at 6:53 am
Hi experts,
I need serious help. We cannot find the cause of our transaction log suddenly growing out of control. At 10PM we can back it up and truncate it to 2 MB and by 8AM it is up to 3 GB. This has been happening for 10 days now. Scheduled jobs that ran before the problem continued to run for several days after the problem first exhibited. We have stopped the Veritas backup, initiated a SQL Server backup followed by a truncate, and set up growth limits. We think the log file hits the new limits so fast that the scheduled jobs (which run 9.5 hrs) just don't have a chance to complete before the log fills up again so those jobs are not succeeding anymore.
The transaction log doesn't seem to have any entries to suggest anything, not even why it is so big. Any suggestions on what to monitor? Please help!
December 27, 2007 at 9:32 am
The log changes based on data changes.
You can copy off a log backup and then use Log Rescue from Red Gate (I work for them, the tool is free) to read the logs and see what data changes are being made. Or you can run Profiler and watch what is happening.
My guess is this was always happening, but someone had the database mode set to SIMPLE, so the changes were being thrown out.
You might need a 3GB log. Three is no set size. The log should be sized based on the amount of transactions that occur in a particular time. Have you set up log backups? You could run these a few times a day and see what their size is.
December 27, 2007 at 11:19 am
Transaction log might got filled if there is any open transaction remains...if you are able to truncate the log then this is not the issue...
Looks like its some job you are running in night which is causing this...so try to run limited jobs and see which one is causing issue...
any other environment change setting change?? hard disk good?
check what transactions are running with dbcc inputbuffer to guess what could be the reason...it might be a big insert activity...
BOL...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 27, 2007 at 11:49 am
Hi Steve,
Does Log Rescue show more than what we see in Enterprise Manager-SQL Server Logs?
December 28, 2007 at 6:12 am
Steve,
We tried Log Rescue and have a question. If a procedure opens a cursor and brings in a result set of 100,000 records then enters a while loop which will test for the presence of each record in the destination table (using an if-present-update-else-insert logic) and either update the record in the destination table or insert the record into the destination table why do we not see (when using the Log Rescue tool) 90,000 updates and 10,000 inserts? Thank you.
Warm regards,
Hope
December 28, 2007 at 9:47 am
Enterprise Manager/ SQL Server logs shows the instance error log. NOT the transaction logs.
Log Rescue reads logs from the transaction logs. I'll have to pass your question along as I'm not an expert. I'll get Dr. Andras to respond.
January 2, 2008 at 4:14 am
h.schlais (12/28/2007)
Steve,We tried Log Rescue and have a question. If a procedure opens a cursor and brings in a result set of 100,000 records then enters a while loop which will test for the presence of each record in the destination table (using an if-present-update-else-insert logic) and either update the record in the destination table or insert the record into the destination table why do we not see (when using the Log Rescue tool) 90,000 updates and 10,000 inserts? Thank you.
Warm regards,
Hope
Hi,
does the transaction actually commit or is it rolled back? Log Rescue will show you only committed transactions. If it is not rolled back, what does Log Rescue show you? An update is not always an update (for example if you update a column that is part of the clustered index, the update is turned into a delete and an insert).
Regards,
Andras
January 2, 2008 at 10:45 am
Can you not restore the database to a test box and run each job that is scheduled to run every night to see which one causes the growth?
3GB is not much at all (well unless you have space issues). Also, if the log is growing to 3GB, I'd not shrink it - you'll only incur extra IO with the log file having to grow every night.
Do you have any processes that insert data via linked servers? Although the log file would grow on your production box, the job could well exist on another - this has caught me out in the past 🙂
And finally, what is the frequency of your log backups? If they were hourly, for example, and the 1am & 2am files were a regular size and the 3am & 4am backups were a lot bigger, you could narrow down the window and look for all scheduled processes running between 2am and 4am.
January 2, 2008 at 1:09 pm
You haven't recently turned on transactional replication have you? That may cause your log size to increase. Also, Index reorganization and rebuilding (depending on your recovery model) are fully logged, so if you recently added tasks to do this on a regular basis, you could also be seeing the results from that.
Hope this Helps.
January 3, 2008 at 7:50 am
How big is the database getting the 3GB logfile? 3GB may be reasonable, especially with 100,000 record inserts.
Is there a scheduled task that reindexes the database regularly?
January 8, 2008 at 12:32 pm
Thank you all, Prakash, Steve, Andras, Clive, Jeremy, and Andrew.
OMG, I needed all your ideas and questions. The critical issue has partially resolved itself almost as mysteriously as it appeared. During the two weeks I was plagued by the transaction log I followed up on your comments and learned quite a bit. One week into the problem we created a job to automatically backup/truncate/shrink the transaction log hourly. That took my 9hr overnight sync job to 17hrs. I implemented a job to monitor and record the size of the log file (as suggested) every 15 minutes. When I realized the log file was never growing before it was truncated I change the hourly to a conditional backup/truncate/shrink based on the size of the log. What we have found now is that the log is no longer growing and after 2 days the truncation has never had to run.
Summarizing, the beginning of the problem was a transaction log file that grew to 11GB and after truncating and shrinking it did it again and again over the next three days. It has stopped growing but we haven't come to an agreement on the likely cause so the conditional truncate job will stay in place for a long while.
Benefits, I learned a lot more a lot faster.
Thank you all and
Warm regards
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply