myukas - Monday, November 20, 2017 6:58 PM
If it's for 80% of a DB limit, then it's easy. Have a look at sys.master_files. If you divide page counts by 128, that will give you values in megabytes.
If it's for % of disk space free because of unlimited or insanely high limit values, then you need to make a call to the operating system for disk free space information. That, of course, can be done many ways depending on which caveats you want to pay attention to. It could be done through PowerShell, xp_CmdShell, a Cmd Exec Task in a job (I supposes), or by using the undocumented xp_fixeddrives (which some people have some difficulty with "mount points" IIRC but works fine for the way our SAN is configured to present the drives to SQL Server).
The rest is just simple arithmetic play against those sources.
I do have a stored procedure I built that collects disk information from the 285 servers and other systems that I've been required to monitor but you wouldn't want to see it. You probably wouldn't want it because I did it on a dare just to tick off some serious anti-xp_CmdShell zealots that preferred to use PowerShell only and didn't know how to spell WMI by using xp_CmdShell to call PowerShell to call WMI in a loop to return the output through PowerShell only to the standard output device and then split the return of xp_CmdShell output to put in a table using persisted computed columns. 😉 It even finds "lost" removable media like CDs, memory sticks, etc. Then, to add insult to injury, I format it and color code the morning report from that derivative in HTML using XML trickery through T-SQL. Heh... it's an intentional "Tower of Babel" that breaks every sensitivity I could think of but is surprising fast. I also have 6 years of history stored in a big ol' monolithic table to tick off the people that say you have to partition a monolithic table of that size to get any performance out of it. If I ever get around to stop laughing about it, I'll rewrite it to skip the PowerShell part of it (I thinks that's slowing it down a bit) and do a partitioned view instead of a monolithic table just so I don't have to backup that which will never change after the first backup. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.