How to Monitor Transaction Log Usage In SQL Server?

I would like to monitor transaction log usage, regarding all the following aspects:

-
What Task\Job\Query is making it fill the drive\file.

-
Log file usage percentage.

-
What time the transaction happened.

Any relevant way of doing this which is tested would be helpful.

The easiest way is to buy an off-the-shelf monitoring tool. They all give you this kind of information - Idera SQL DM, Quest Spotlight, SentryOne SQL Sentry do these kinds of things with really low impact.

The next easiest way is to build something yourself. If you're going that route, I'd start by logging sp_WhoIsActive to a table - especially with the @get_transaction_info = 1 switch.

If you try the roll-your-own approach, you need to be aware that queries aren't the only thing that will cause the transaction log to grow. For example, if you're using replication, database mirroring, or Always On Availability Groups, SQL Server needs to retain history when one of those replicas is offline. To learn more about what's causing that, check log_reuse_wait_desc:

SELECT name, log_reuse_wait_desc FROM sys.databases;

StackExchange Database Administrators Q#177369, answer score: 8