SQL Server Performance Forum – Threads Archive
Large Transaction Log
we have an issue where our database has suddenly started producing very large transaction logs. The log is backed up every 10 minutes, and has is currently averaging around 1gb per backup. Any ideas/suggestions on how to track down what is causing the log files to grow so rapidly??<br /><br />we do have a number of jobs that run regularly, and have also added an indexed view or 2 recently if that helps!<br /><br />Thanks in advance<br /><br />Ben<br /><br />PS hope everyone has a great Easter <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> Extra long weekend in my part of the world!!!<br /><br />’I reject your reality and substitute my own’ – Adam Savage
is this database involved in replication ?
A good point to checkout, also check whether you are performing any online index operations.
If you are running any scheduled jobs such as bulk load operations or rebuild or update stats of a table that will have knock on aaffect on the size of TLog. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hey Guys,
Nope, the database isnt involved in replication…i think someone did add an index rebuilding job recently, but as far as i knew it was running outside of work hours! will have to check that one out – thanks! Any other ideas are more than welcome! Ben ‘I reject your reality and substitute my own’ – Adam Savage
The rebuilding job must be the reason.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
Check for any open transactions, if there is any check the inputbuffer of those open trans… Is database recovery changed? A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
http://support.microsoft.com/kb/317375
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi,
Refer below links too http://support.microsoft.com/?id=110139 http://www.support.microsoft.com/?id=317375 How to stop the transaction log of a SQL Server database from growing unexpectedly http://support.microsoft.com/?kbid=873235 HTH
Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Ben I too had the same problem because of index rebuild happening frequently. That’s the most common reason why the log grows too large. Check if that is troubling ur log Satya
If so check whether that index is enabled with ONLINE REINDEXING that will have this consquences. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>