Hey folks, and welcome to my first proper blog post. One of the things that I like to monitor through my daily checks in the file size and the free space of each file within the databases. During a busy loading period, some databases can grow massively, so it’s important to keep an eye on the growth. Through the UI, SSMS only gives us the size of the files and what the max size is that they can grow to, which is clearly not very useful. Fortunately,  a quick query on the dbo.sysfiles of each databases that we want to monitor gives us some info:

Here is the full query:

select
        sf.FILEID AS [File ID],
        [File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)),
        [Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)),
        [Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)),
        [Free Space in MB] = convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)) ,
        [Free Space in %] = convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)) ,
        [File Name] = left(sf.NAME,30),
        [File Location] = left(sf.FILENAME,100)
        from dbo.sysfiles sf
        order by fileid asc