|A log file has been growing very fast from 2 gig up to 19 gig in a matter of about 4 hours.
The recovery model is set as full.
Even with backing up the transaction log file, every 30 minutes, I have found that the log file is not getting truncated.
I did notice that the file growth stopped as soon as the workday came to a close.
DBCC SQLPERF(LOGSPACE) I noticed that the log space was being eaten up fairly quickly. Also on running
DBCC OPENTRAN I did not see any really old transactions that had not been commited
In my investigations someone mentioned that if a person was selecting a large volume of data this could make the log file grow. It is quite possible to select in the region of 9million plus rows in a select query of this database.
I was under the impression that the transaction log file only contained updates, deletes and inserts in order to be able to rebuild the database at any point in time. Having googled this I am not any the wiser.
I will run some more diagnostic tests on Monday to see if I can discover any more information if I see the log file growing rapidly again.
So my question is – would a large number of individual select commands, or a high volume of data being selected get logged to the transaction log file?
Solved - read below
It turns out that a user was running a large report which kept falling over.
They kept restarting the report.
The software is a 3rd party piece of software that appears to write results to some sort of temporary table.
I asked the user to run the report first thing this morning, when the office was very quiet.
DBCC SQLPERF(LOGSPACE) I then noticed the log starting to be eaten up again and when the report completed the log stopped growing.
So I think I am going to set up some sort of warning system that monitors log growth over time and alerts me when it starts ballooning as it did on Friday.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 6-Oct-14 8:04am.