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 (%):
MSSQLTips.com delivers SQL Server resources to solve real world problems for DBAs, Architects, DevOps Engineers, Developers, Analysts, Cloud and Business Intelligence Pros – all for free. The content we serve is all human written and based on our authors’ real-world experience. Check out tips, articles, scripts, videos, tutorials, live events and more all related to SQL Server on-premises and in the cloud.
