The batch below will reveal all the information you want for the log files of the databases in current instance. Recovery model, location, size, used log, disk available on log file disk, last log backup, log size since last log backup, log reuse wait, VLFs etc…<\/p>\n\n\n\n
declare @dbname nvarchar(100),@sql nvarchar (1000);\ndeclare dbs cursor for\nselect name from sys.databases where state_desc='ONLINE';\nIF OBJECT_ID('tempdb..#logUse') IS NOT NULL\n DROP TABLE #logUse\ncreate table #logUse ([Database Name] varchar(100),[Log File] VARCHAR(200),[Recovery Model] varchar(10), [Log Reuse Wait] VARCHAR(50),[Disk Total GB] INT, [Disk Available GB] INT, [Log Total MB] int,[Log Used MB] int,[Log Used %] smallint,\n[Log MB since las backup] int,[Last Log Backup Start] datetime,[Last Log BackupEnd] datetime, [Last Log Backup MB] int);\nOPEN dbs;\nFETCH NEXT FROM dbs into @dbname;\n\nWHILE @@FETCH_STATUS=0\nBEGIN\nset @sql=N'insert into #logUse\nselect top 1 db_name(SP.database_id),F.physical_name,D.recovery_model_desc,D.log_reuse_wait_desc,OS.total_bytes\/1024\/1024\/1024,OS.available_bytes\/1024\/1024\/1024,total_log_size_in_bytes\/1024\/1024 ,\nused_log_space_in_bytes\/1024\/1024 ,ROUND(used_log_space_in_percent,2) ,\nlog_space_in_bytes_since_last_backup\/1024\/1024,backup_start_date,backup_finish_date,round(backup_size\/1024\/1024,0) from ['+@dbname+'].sys.dm_db_log_space_usage sp JOIN SYS.DATABASES D ON D.database_id=SP.database_id\nJOIN sys.master_files f ON F.database_id=SP.database_id AND F.type_desc=''LOG'' left join msdb.dbo.backupset bs on bs.database_name=d.name and bs.type=''L'' CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) OS\norder by bs.backup_finish_date desc'\n\n--print @sql\nexec sp_executesql @sql\nFETCH NEXT FROM dbs into @dbname; \nEND;\nclose dbs;\ndeallocate dbs;\n\nselect l.*, vlf.[VLF Count],vlf.[Active VLF],vlf.[Active VLF Size (MB)],vlf.[In-active VLF],vlf.[In-active VLF Size (MB)],case when vlf.[VLF Count]>100 then 'Perhaps you should Shrink and recreate Log File' else 'VLF count is not dangerously high' end as 'VLF Status'\nfrom #loguse l join (SELECT [name], \nCOUNT(l.database_id) AS 'VLF Count',\nSUM(CAST(vlf_active AS INT)) AS 'Active VLF',\nSUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',\nCOUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',\nSUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'\nFROM sys.databases s\nCROSS APPLY sys.dm_db_log_info(s.database_id) l\nGROUP BY [name], s.database_id\n) vlf on vlf.name=l.[Database Name]\ndrop table #loguse\n<\/code><\/pre>\n\n\n