January 5, 2016 at 6:16 am
On my Prod server at 5 am log file grows more than 40% and later goes back to around 0% later. How to find out what happens that time which makes log so big?
January 5, 2016 at 6:27 am
Check SQL Server Agent, see if any jobs are scheduled to run at that time.
You could also create an alert that fires if the log size increases over a certain amount - this alert could then trigger a job which inserts performance data e.g. active transactions into a logging table for you to review
January 5, 2016 at 6:54 am
my question is, looking at the log file size at certain time, how to find what occurred that time such as insert, delete, ope tran etc... is that any way to find
January 5, 2016 at 7:21 am
How about using the Profiler?
January 5, 2016 at 7:22 am
Hi,
maybe you can use the profiler to scan your activities on the server in the night.
Or just use the dmv, to identify the queries with the most IO / CPU.
I hope it is okay, to post this link:
http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/
Kind regards,
Andreas
January 5, 2016 at 3:15 pm
First of all thanks guys for your replies. I think I need to be more clear. just say, out of the blue I found A log file (.trn) is very big from log backup job.
Is there anyway to find what caused the log file to be that big?
January 5, 2016 at 4:51 pm
Read through the default trace file to see if you can find the activities happened during the period. Or else, if you have any monitoring tool say, foglight/any other, check out what happened!
Thanks.
January 6, 2016 at 5:41 am
You can query the default trace for autogrowth activities.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply