How Long Before Your Database Runs Out of Space?

  • Over the years have seen many solutions that use SQL Express on the back end.  We started using this
    The best part about DB Health assist is that it will notify you of potential issues before they occur; like if you are about to run out of disk, or if your disk is starting to fail. 
    http://www.complianceabc.com/dbhealthassist.aspx

    Tom Romeo
    Practical Compliance Solutions Corp.
    One Orient Way - Suite F156.
    Rutherford, NJ 07070-2524
    --------------------------------
    www.complianceabc.com
    email: tromeo@complianceabc.com
    Phone: 201-728-8809

  • Nice piece of code.  
    Our Minion Enterprise product does this and much more for your entire enterprise without installing anything at all on any of your servers.
    http://minionware.net

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Great Article Sir. Thanks.

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • Hi,
    this article is interesting.
    Could you please tell me if I'm wrong but the formula you defined:

    Days Remaining = (Free space in files) + (Growth Increment * Number of Growths remaining) / (Growth per Day [averaged])


    should be:

    Days Remaining = [(Free space in files) + (Growth Increment * Number of Growths remaining)] / (Growth per Day [averaged])

    isn't it ?

  • Jefferson Elias - Thursday, August 24, 2017 6:43 AM

    Hi,
    this article is interesting.
    Could you please tell me if I'm wrong but the formula you defined:

    Days Remaining = (Free space in files) + (Growth Increment * Number of Growths remaining) / (Growth per Day [averaged])


    should be:

    Days Remaining = [(Free space in files) + (Growth Increment * Number of Growths remaining)] / (Growth per Day [averaged])

    isn't it ?

    Correct - the extra set of parentheses are well placed.

  • robert.nimstroem - Thursday, June 9, 2016 12:26 AM

    Nice.But you should also inform people looking at this that the user running this script has to have the sysadmin role and that xp_cmdshell and OLE AUTOMATION must be turned. on.

    You don't think people that are responsible for obtaining this type of information won't realize that when they read the script?  You also make it sound like being a member of the sysadmin role would be a problem for the people that would need the output of these scripts.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Joe O'Connor - Friday, May 12, 2017 5:53 AM

    SQLBlimp - Wednesday, June 8, 2016 10:23 PM

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

    Thanks for sharing this!  I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity.  It does not take SQL File growth size into account, so there may be room for improvement

    I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂

    SELECT DISTINCT
       @@SERVERNAME AS ServerName
      , vs.volume_mount_point AS VolumeName
      , vs.logical_volume_name AS VolumeLabel
      , vs.total_bytes AS VolumeCapacity
      , vs.available_bytes AS VolumeFreeSpace
      , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent
    FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

    "Gets you away from xp_CmdShell".  Why do you think the use of xp_CmdShell is a problem?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • And, yes, I realize this article is from last year but it's new to me.  Nice job, John.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden - Monday, October 23, 2017 8:07 PM

    Joe O'Connor - Friday, May 12, 2017 5:53 AM

    SQLBlimp - Wednesday, June 8, 2016 10:23 PM

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

    Thanks for sharing this!  I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity.  It does not take SQL File growth size into account, so there may be room for improvement

    I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂

    SELECT DISTINCT
       @@SERVERNAME AS ServerName
      , vs.volume_mount_point AS VolumeName
      , vs.logical_volume_name AS VolumeLabel
      , vs.total_bytes AS VolumeCapacity
      , vs.available_bytes AS VolumeFreeSpace
      , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent
    FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

    "Gets you away from xp_CmdShell".  Why do you think the use of xp_CmdShell is a problem?

    It's like a little doggy door to the house - If you have one, something is bound to try to get in.  I would prefer not to enable it if you can do it a different way.

  • I also want to know why you think cmdshell is a problem.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Joe O'Connor - Tuesday, October 24, 2017 6:56 AM

    Jeff Moden - Monday, October 23, 2017 8:07 PM

    Joe O'Connor - Friday, May 12, 2017 5:53 AM

    SQLBlimp - Wednesday, June 8, 2016 10:23 PM

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

    Thanks for sharing this!  I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity.  It does not take SQL File growth size into account, so there may be room for improvement

    I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂

    SELECT DISTINCT
       @@SERVERNAME AS ServerName
      , vs.volume_mount_point AS VolumeName
      , vs.logical_volume_name AS VolumeLabel
      , vs.total_bytes AS VolumeCapacity
      , vs.available_bytes AS VolumeFreeSpace
      , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent
    FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

    "Gets you away from xp_CmdShell".  Why do you think the use of xp_CmdShell is a problem?

    It's like a little doggy door to the house - If you have one, something is bound to try to get in.  I would prefer not to enable it if you can do it a different way.

    Only people with sysadmin privs can enable it.  Only people with sysadmin privs can use it (unless you've made the terrible mistake of allowing non-DBAs to use it with or without a proxy).  If an attacker get's in but doesn't attain sysadmin privs (one way or another), they can't use xp_CmdShell.  If they do get in with sysadmin privs , having it disable does NOTHING for security except cause a couple of millisecond delay in their attach software.  Further, if they make it in with sysadmin, they don't even need xp_CmdShell because they can, quite literally, do anything they want.

    Turn on xp_CmdShell and use it.  If that makes you paranoid because you need to be paranoid about who gets sysadmin privs either intentionally or during an attack.  xp_CmdShell isn't a security risk.  Bad security is the security risk that you need to be paranoid about.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Following up on what Jeff said, if you're worried about cmdshell because there are people in sysadmin who don't belong there and you don't want them to be able to mess anything up with it, then make sure you've got the service acct locked down.  Then even if they use it, they won't be able to do anything bad.  But if you're worried about cmdshell then you'd better close the other features that are along the same lines... cause while everyone is working on making sure cmdshell is locked down, they're doing nothing for the agent or SSIS, or linked servers, or startup SPs, etc.  Cmdshell gets the brunt of it cause it was installed open in SQL2K and below.  But it's not the only attack vector when your security is bad.  So focus on fixing your actual security issues and the rest will fall into place.
    And btw, I too always say that you need sysadmin to run it, but you really only need CONTROL SERVER.  But since I've never ever seen anyone assigned that perm by itself, it's safe to say you need sysadmin.
    Here's a blog i wrote on the topic a while back: http://www.midnightdba.com/DBARant/security-theater/

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Jeff Moden - Tuesday, October 24, 2017 4:50 PM

    Joe O'Connor - Tuesday, October 24, 2017 6:56 AM

    Jeff Moden - Monday, October 23, 2017 8:07 PM

    Joe O'Connor - Friday, May 12, 2017 5:53 AM

    SQLBlimp - Wednesday, June 8, 2016 10:23 PM

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

    Thanks for sharing this!  I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity.  It does not take SQL File growth size into account, so there may be room for improvement

    I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂

    SELECT DISTINCT
       @@SERVERNAME AS ServerName
      , vs.volume_mount_point AS VolumeName
      , vs.logical_volume_name AS VolumeLabel
      , vs.total_bytes AS VolumeCapacity
      , vs.available_bytes AS VolumeFreeSpace
      , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent
    FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

    "Gets you away from xp_CmdShell".  Why do you think the use of xp_CmdShell is a problem?

    It's like a little doggy door to the house - If you have one, something is bound to try to get in.  I would prefer not to enable it if you can do it a different way.

    Only people with sysadmin privs can enable it.  Only people with sysadmin privs can use it (unless you've made the terrible mistake of allowing non-DBAs to use it with or without a proxy).  If an attacker get's in but doesn't attain sysadmin privs (one way or another), they can't use xp_CmdShell.  If they do get in with sysadmin privs , having it disable does NOTHING for security except cause a couple of millisecond delay in their attach software.  Further, if they make it in with sysadmin, they don't even need xp_CmdShell because they can, quite literally, do anything they want.

    Turn on xp_CmdShell and use it.  If that makes you paranoid because you need to be paranoid about who gets sysadmin privs either intentionally or during an attack.  xp_CmdShell isn't a security risk.  Bad security is the security risk that you need to be paranoid about.

    Jeff, your writing and articles have always been helpful, so I give some weight to what you say, so I'm a bit at a loss as to why you're attacking my preference of utilizing other methods to get the exact same results.  That would be like me replying to one of your comments that someone should use cursors even if there are set-based ways to accomplish the same results.  Your points are all valid about how it's not the security hole as I suggested it was, so enable it, disable it, do whatever you like so long as you recognize that its acceptable to not use it too.

  • I don't believe Jeff was attacking anything.  And I think I can speak for both of us when I say that we really don't care what you use for something like this.  But you said cmdshell was unsafe and that's what he was arguing with... and me too.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • KenpoDBA - Wednesday, October 25, 2017 7:06 AM

    I don't believe Jeff was attacking anything.  And I think I can speak for both of us when I say that we really don't care what you use for something like this.  But you said cmdshell was unsafe and that's what he was arguing with... and me too.

    I never said "unsafe", but to blanket call it "safe" would give the wrong impression.  It is adding fuel to the fire if a system is not configured with good security practices.  I'd love to tell you I've never seen or heard someone suggest that the service account needed to be a local administrator, or that users/developers had the ability to modify SQL agent jobs.  (aside: xp_cmdshell to ping a server as a form of monitoring had me laughing and crying).  There's still a lot of people who believe that "SQL is easy, so I can do it" - and then the proceed to do it wrong.  I would not want to have someone new to administering SQL find this thread and walk away enabling xp_cmdshell when it isn't necessary to do so.

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply