April 6, 2008 at 8:27 pm
I have a maintenance plan that backs up our main database transaction log every 2 hours. Every couple of month, I get a very large (35gb) transaction log backup file. It is not traceable to database activity and in the last case it happened at 4am when there was little or no activity. Any ideas?
April 6, 2008 at 9:10 pm
check your maintenance plans for other activity. Things like rebuilding your indexes can have exactly this effect.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2008 at 9:36 pm
No activity other than the backup maintenance plan in the log.
April 6, 2008 at 10:31 pm
Any other Jobs?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2008 at 7:49 pm
If i backup transaction log every 2 hours and the log file size grow until 35 gb
I will
Check backup size of log file which i backup every 2 hours.
one day I will have 12 log file, and I will check which log file is largest compare to other.
example
2.00 300k
4.00 298k
6.00 2g
8.00 286k
10.00 300k
Then i will run profiler to trace what happen from 4.00 - 6.00
Cheers,
soonyu
April 8, 2008 at 7:48 am
Running profiler after that fact isn't going to show you anything. There is definitely something running as was suggested. A maintenance plan rebuilding indexes or large data loads (you can check row counts periodically to see spurts in growth). I'd check all scheduled jobs first. If that doesn't yield anything, then possibly setup profiler. It can be resource-intensive so if you can narrow down what you're looking for (or when), it'll be less taxing on your system.
-- You can't be late until you show up.
April 8, 2008 at 10:42 am
The issue was tracked down to a maintenace job. Thanks for all your help.
April 8, 2008 at 11:22 pm
You're welcome.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply