Log file space available

-- (with Mark Nelson)

USE master

GO

 

Create Table ##temp

(

    DatabaseName sysname,

    Name sysname,

    physical_name nvarchar(500),

    FileSizeMB decimal (18,2),

                SpaceUsedMB decimal (18,2),

    FreeSpaceMB decimal (18,2)

)  

Exec sp_msforeachdb '

Use [?];

Insert Into ##temp (DatabaseName, Name, physical_name, FileSizeMB, SpaceUsedMB, FreeSpaceMB)

    Select DB_NAME() AS [DatabaseName], Name,  physical_name,

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) FileSizeMB,

   

    Cast(Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) as SpaceUsedMB,

 

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -

        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpaceMB

   

    From sys.database_files

'

Select * From ##temp WHERE right(physical_name,3) = 'ldf'

drop table ##temp