Monitor transaction log

We can monitor the transaction log for all databases in an SQL Server Instance based on the SQL Statement:

DBCC SQLPERF (LOGSPACE)

Following is the full script, we save log information of each database to a table, and can set up SQL Agent to send email every day, so we can monitor the transaction log.

CREATE TABLE dbo.logSpaceUsage
(
id INT IDENTITY (1,1),
logDate DATETIME DEFAULT GETDATE(),
databaseName SYSNAME,
logSize DECIMAL(18,5),
logSpaceUsed DECIMAL(18,5),
[status] INT
)
GO
INSERT INTO dbo.logSpaceUsage
(databaseName, logSize, logSpaceUsed, [status])
EXEC ('DBCC SQLPERF(logspace)')
GO
SELECT *
FROM dbo.logSpaceUsage
GO

For example: here is the result in my laptop:

SQL Server - Monitor Transaction Log

Please note that status always = 0

Refer:

• http://blog.sqlauthority.com/2012/07/03/sql-server-monitoring-sql-server-database-transaction-log-space-growth-dbcc-sqlperflogspace-puzzle-for-you/
• http://technet.microsoft.com/en-us/library/ms189768.aspx