Monitoring the log file is very important, and SQL Server has made it fairly easy for us to do this. One way to find information about the log is in the catalog view sys.database_files. This view returns information about data and log files that include the type of file, name, location, state, size, growth, etc.
Explanation
Getting Log File Size Information
The following query will filter down to only the log file and displays some very useful information:
SELECT
name AS [File Name],
physical_name AS [Physical Name],
size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB],
[growth],
[file_id]
FROM sys.database_files
WHERE type_desc = 'LOG'
You can also use DBCC SQLPERF (‘logspace’), which has been around for a while. This command displays useful details such as DB name, Log Size (MB), and Log Space Used (%):
