To keep track on database transaction log growth, you can use DBCC SQLPERF command that monitors transaction log space used in the database. Log file size is depends on recovery model of database and backup job. Frequently running transaction log backup can keep transaction log in good shape.
Sql server has dbcc sqlperf command to track the transaction log size.
DBCC SQLPERF(logspace)
Ouput
You can run the schedule job that sent you email with output.
OR
Four columns are there in output, you can capture this data in table on regular basis then generate the report using captured data.
CREATE TABLE dbo.GetlogSpace
(date datetime default getdate(),
dbname varchar(50),
log_size decimal(10,5),
log_used decimal(10,5),
status int
)
Now create a stored procedure to capture the log details using dbcc sqlperf(logspace) command
CREATE PROCEDURE sp_getLogspace
AS
BEGIN
INSERT INTO GetlogSpace(dbName, log_size, log_used, status)
DBCC SQLPERF(logspace)
END
Now schedule a job to run above stored procedure on alternate days to generate capture data and insert into table.