Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-06 : 08:38:24
|
| Sabeena writes "Hi,I'm new to SQL Server and have come across what I think may be a problem and would greatly appreciate some advice. There is a job running on one of the databases which does a transaction log back up every 45mins and a full back up every 1 day. I've had a look at where the transaction log back ups are kept and it seems that every so often one of these files will be incredibly large. The average size for the files seem to be 50-90MB, however on occasion (7 times in the last two days) the size of a log file will be between 3 and 7GB. To me this seems strange because some of these larger files have been created when there would not have been many transactions occurring. If you have any ideas as to how I can investigate this further and deal with the issue I'd be very grateful.Thanks," |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-06 : 15:15:01
|
| See if you have DBCC DBREINDEX (or if you use the maintenance plans, then it's called Optimizations) running during this time.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-06 : 18:19:34
|
| Could the automatic Update Statistics be causing that much log data too?If its the same time each day then its more likely to be a scheduled taskKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-06 : 19:53:55
|
| From the testing that I have done with UPDATE STATISTICS, it does not log very much if at all. It was so small that I couldn't tell if it was just normal activity going on.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-07 : 01:24:18
|
| Thanks for clarifying that Tara. Before we started using our own maintenance routines something took our databases our of action for around 30 minutes at a time - and at unpredictable times. I suspect the Auto Stats routines, and I kinda assumed that 30 minutes intense effort would generate some logging!Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-07 : 02:41:11
|
| do you have an optimization job (maybe created using the sql maintenance plan wizard) running every day or so? -ec |
|
|
|
|
|
|
Some Transaction Log Back Ups are Really Big