(There is a more up to date version of this script here)
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:
but this isn’t entirely too useful. For starters the size is in KB. This makes sense as databases store data in 8kb sized files. Whilst it may be OK for a small database like this one, our data warehouses are far to big for us to find sizes in KB useful. Also, we can infer the remaining space, but again it’s not too helpful by having to figure this out for ourselves.
So if we use a query that converts the size of the files into MB, then we have something a little more pertinent to a data warehouse. We can calculate the size in MB by using the following formula:
- File Size in Mb = (Kb) / (Kb per Mb) / (Kb per Page)
- File Size in Mb = (Sys.Size) / 1024/ 8
- File Size in MB = Sys.Size /128
And since 128 is going to be a constant we can use this throughout our query when calculating max size in MB.
Now we’re getting somewhere. But we’re still not getting the numbers for how much space there actually is in each file which really helps us. By using the FILEPROPERTY function ‘SpaceUsed’, we can determine how much space is used. When using the FILEPROPERTY function, it requires two parameters: file name and property.
Again we divide by 128 so that the number is in MB. So now if we want to see how much free space we have we just subtract the number outputted from the ‘SpaceUsed’ function from the File Size:
and if we really want to show off (and who doesn’t?) then if you use the formula Free Space In Percent = 100 * (Free Space/128) /( Total Space/128) then you can display the Free Space in %.
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
In a follow up post I will talk about the script I use to put this into a table and send it as part of my daily emails. That’s all for now, I hope you find this useful!