• myukas - Monday, November 20, 2017 6:58 PM

    80% of max usage if a limit and possibly if unlimited growth and grows by X amount not sure what X should be yet

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)