SQL Server 2008 R2 log growth question - solved!
-
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. On running
DBCC SQLPERF(LOGSPACE)I noticed that the log space was being eaten up fairly quickly. Also on runningDBCC OPENTRANI 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. UsingDBCC 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
-
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. On running
DBCC SQLPERF(LOGSPACE)I noticed that the log space was being eaten up fairly quickly. Also on runningDBCC OPENTRANI 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. UsingDBCC 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
Have a look at the
log_reuse_wait_desccolumn insys.databases- that should give you some idea of what's preventing the log from being truncated.SELECT
[name],
recovery_model_desc,
log_reuse_wait_desc
FROM
sys.databases
;Technet has a description of the values: http://technet.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Have a look at the
log_reuse_wait_desccolumn insys.databases- that should give you some idea of what's preventing the log from being truncated.SELECT
[name],
recovery_model_desc,
log_reuse_wait_desc
FROM
sys.databases
;Technet has a description of the values: http://technet.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks - yes I was looking at this on Friday. I discovered it was a user who was running a report that kept falling over and they kept restarting it. The software is a 3rd party piece of software and it looks like when users select data the results are written to some sort of temporary table within the database.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
