How to monitor transaction log growth


By:

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 type of file, name, location, state, size, growth, etc. 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'
        


How to monitor transaction log growth.

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 (%):

This command displays useful details such as DB name, Log Size (MB) and Log Space Used (%)

Last Update: 3/25/2014




Comments For This Article





download














get free sql tips
agree to terms