June 4, 2016 at 1:14 am
I am in a need to track database log file growth. I googled a lot but did not get the right way to track what is causing the log file to grow so rapidly in one of our databases. Could someone please let me know if there is any script or profiler events to not only track the autogrowth event, but also what caused it to grow.
Thanks
Regards,
Fievel
June 4, 2016 at 3:53 am
ffarouqi (6/4/2016)
I am in a need to track database log file growth. I googled a lot but did not get the right way to track what is causing the log file to grow so rapidly in one of our databases. Could someone please let me know if there is any script or profiler events to not only track the autogrowth event, but also what caused it to grow.Thanks
Regards,
Fievel
The simplest way is to query the default trace for file growth events, plenty of examples around on how to do that. You could also add an extended event for this, slightly more to learn but even simpler to use.
😎
June 4, 2016 at 6:15 am
you can use the DBCC SQLPERF(LOGSPACE)
is there any reason you want to captured the log file growth
June 4, 2016 at 11:41 pm
hi
there are 3 reasons to cause log file growth.
1) long running query (or) bulk transactions done at a time.
2)transaction log backups are not taken (or) not running.
3)active VLFs.
1ans: need to verify the query . bulk transactions can be divide into simple
eg: 20gb transactions can be divide into 5gb...
2ans: need to take logbackups.
3ans: active VLFs formed in case secondary server down...need to verify.
June 5, 2016 at 2:26 am
I agree with all your methods, but this does not provide me an answer to my original question asked. Basically, I could get the details of the log growth even happening, but I need to know what caused it for example. Is it some code that is causing it, or a SQL job or some process etc. I need those granular details in order for me to provide some kind of tracking for the client. The client is concerned that his log file backup which is backed up every 20 minutes in a 24 hour cycle has been increased in size due to something wrong going on. Hence, I've been tasked to find out the culprit.
Regards,
Feivel
June 5, 2016 at 10:05 am
hope it may usefull
select log_reuse_wait_desc from sys.databases
result
0 = Nothing
1 = Checkpoint, waiting for a checkpoint to occure
2 = Log backup, waiting for a log backup to occur
3 = Active backup or restore, A backup or restore operation
is running on the database
4 = Active transaction,
There is an active transaction that needs to complete
(either way - ROLLBACK or COMMIT) before the log can be backed up
5 = Database mirroring
Either a mirror is getting behind or under some latency in a high performance mirroring
situation or mirroring is paused for some reason
6 = Replication
There can be issues with replication that would
cause this - like a log reader agent not running
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica
is applying transaction log records of this database
to a corresponding secondary database
June 7, 2016 at 5:00 am
When you say track what do you really mean? Do you want a report daily if the ldf file grows? Or do you want something much longer term?
June 13, 2016 at 1:54 pm
I am sitting on a non-windows machine at the moment so can't offer a solution. Have you looked into using extended events to try and capture what you need? I've had some success capturing blocking information with this.
June 14, 2016 at 8:52 am
In SQL agent there is a alert which can be scheduled to alert after particular size file growth.
additionally restrict the log file to the threshold as per company policy and once your alert is triggered you can check the running sessions. on a issue even i tried to set one but no direct query i can find. even i will wait for answers on this thread.
Regards
Durai Nagarajan
June 14, 2016 at 9:51 am
Very simple to setup and use, I still use this script method to this day. This will show you the offending process and can sit on your desktop and run while you work. Runs a piece of sp_who2 and refreshes the window.
http://www.sqlservercentral.com/scripts/Miscellaneous/30099/
July 4, 2016 at 6:41 am
Thanks! everyone, but as I explained earlier I know how to setup log growth events, and alerts. My question if you'll read carefully has nothing to do with setting up an alert to understand log growth. I am not sure if you'll might have faced this issues in your company where suddenly the disk space runs out, and the cause if it seems to be log growth, and the application users, or the Director asks you that why did the log file grew to that size, and you don't have an answer because we don't have logging, so that's what I am trying to ask you'll that is there way I can log those events, and identify based on the logging data for what would be the cause of the log file growing, and the code or the procedure or anything of that nature that is a culprit, so I can present to the management that this is what is the process that has caused that.
Regards,
Feivel
July 4, 2016 at 8:34 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply