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 |
|
phouse
Starting Member
5 Posts |
 Posted - 2010-05-21 : 14:35:43
|
| All:I have a rather perplexing issue that I'm trying to understand. I have a production database that I run hourly transaction log backups against. Each day, the backups are of the same size for a number of hours and then on Sunday, at an irregular time, I get a very large transaction log backup.So for example: the transaction log backups for each day would be like the following -Monday: ... 570 KB, 570 KB, 570 KB, 570 KB, 596 KB, 596 KB, ... (etc)Tuesday: ... 340 KB, 340 KB, 340 KB, ... (etc)...Sunday ... 572 KB, 572 KB, 572 KB, ... , 18 GB, 340 KB, ... (etc)The spread of when the extremely large log back up occurs is large, spanning from occuring at 8 am until 6 pm. No maintenance plans other than transaction log backups occur until 3 am and they complete by 5 am. The only pattern that I've been able to establish is that this only occurs on Sundays and only with this database server. This single database is connected to by a single intranet web-app. I've inquired with the vendor about this and their support says this is not behavior they've seen before and conjectured it was my server.So, my ultimate question is two-fold: what could be causing this and is it something I have control over? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-21 : 14:44:44
|
| Are you rebuilding indexes on sunday? Don't just check maint plans, check the jobs as well.--Gail ShawSQL Server MVP |
 |
|
|
phouse
Starting Member
5 Posts |
Posted - 2010-05-24 : 13:34:00
|
| I found the error. One of the maintenance plans had an erroneous step attached to it that was causing the behavior. I found the step by looking deeper per Gail Shaw's advice. I deleted the step and Sunday passed without a massive transaction log being burped out. Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-24 : 14:53:21
|
| An erroneous step that caused an 18GB Tlog file - I'm curious to know what it was?Are you sure it wasn't needed? sounds like it was the weekly housekeeping routine to me! |
|
|
|
|
|
|
|
|
Odd Transaction Log Backup Behavior