SQL Server Database File Size and Free Space

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

utility_sys_files

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.

dbo_sys_files

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.

space used

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:

free psace

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 %.

freePsaceInPercent

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!

PH

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

5 thoughts on “SQL Server Database File Size and Free Space”

  1. Hi Richie,
    Nice article and I found my needs.
    Also we need one more column USED SPACE in % also would be helpful to have a better clarity USED SPACE AND FREE SPACE in %. Comparatively gud for us.

    Please Send us the script to my mail id : srajeshkumardba@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s