Improved Drive Space Monitoring

  • Comments posted to this topic are about the item Improved Drive Space Monitoring

  • Hi Mark,

    Thanks a ton for this wonderful article. This is exactly what I was looking for since a very long time !

    I am trying to build the .vbs file (which you provided in this article) in SQL itself. I mean I would like to build SAY, C:\Pradeep.vbs file by running a query in QA. I would greatly appreciate your help on this.

    - Pradeep

    Pradeep Adiga
    Twitter: @pradeepadiga

  • what is the purpose of replacing xp_fixeddrives with a call to xp_cmdshell. I would prefer to have xp_cmdshell disabled.

  • As we understand, 'xp_fixeddrives' was really an unofficial xp and we were unsure how long it would be available. So, when we changed the original procedure for SQL 2005 Database Mail we stopped using it. It is totally up to you which method you use, both work..

  • It seems that the vb script won't run on a 64 bit server. Either that or I don't have some configuration right. Is there a work around for this?

  • Sorry to say we have no 64 bit servers yet, so I have not run into this issue. I did a web search on 'running vbs scripts on 64 bit servers' and got a lot of hits discussing this problem. There were several solutions for different problems. I can only suggest you look into these solution as I can not reproduce this problem here....

    Good luck.

  • I realized my problem had nothing to do with 64bit vs 32bit. I had to copy out the code, create a new file on the root of c with no spaces, dashes or underscores, then paste the script into this file. Then I setup the job step as type "Operating System (CmdExec)" and set the command to "call C:\MonitorDriveSpace.vbs" (the quotes were not part of the actual command). I did also add an exception in the script to skip the CDRom as I don't really care to be notified if it has 0% free space which it does because someone left a cd in the drive. On the other hand it does let me know that there is a cd in there...

  • Great! Glad the script is running on your 64 bit system.

    Can you share the code that handles CDROM??:)

  • Of course! Actually I cheated originally and just added AND DiskDrive.DriveLetter <> "D" in the For Each loop. To make the code independent of each machine's actual setup I changed this to If DiskDrive.IsReady AND DiskDrive.DriveType <> 4 Then. However now that I think about it I'm not sure I want to know about any drive if it isn't a fixed drive. So I'll change this to If DiskDrive.IsReady AND DiskDrive.DriveType = 2 Then. This site provided good info on the subject:

  • Thanks. We will look at adding this because CDROM Drives do not interest us at all 🙂

  • Thank you for the practical real world article. But.....

    xp_cmdshell is on the same path as xp_fixeddrives and may be removed. This is a major security risk and needs to be disabled at all costs.

  • I am trying to build the .vbs file (which you provided in this article) in SQL itself. I mean I would like to build SAY, C:\Pradeep.vbs file by running a query in QA. I would greatly appreciate your help on this.

    Somebody pls help me with the above request.

    Pradeep Adiga
    Twitter: @pradeepadiga

  • Eric,

    I agree that xp_cmdshell has security risks and I should have acknowledged this in this article. Thanks for bringing up this point so everyone has a clear understanding of this issue.

  • What do you all think about this method? No intermediate files needed!


    ' Capturues Free and Percent Free Space for all local drives.


    ' ********************************************************************************

    Const ForAppending = 8

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

    '''''''''''''''''''''' Total and Free Space Capture

    Set Drives = FileSystemObject.Drives

    For Each DiskDrive in Drives

    If DiskDrive.IsReady And DiskDrive.DriveType = 2 Then

    driveLetr = DiskDrive.DriveLetter

    totalspace = DiskDrive.TotalSize

    freespace = DiskDrive.FreeSpace

    totalmb = Int((totalspace/1024)/1024)

    freemb = Int((freespace/1024)/1024)

    'D:\Program Files\Microsoft SQL Server\90\Tools\Binn

    RunCmd """for /f ""tokens=* delims=s"" %f in ('dir /ad " & driveLetr & ":\') do @""sqlcmd.exe"" -S DBHostName -Q ""set nocount on; insert into dbatools.dbo.diskfreeimport values('%f');"""""

    End IF


    '' Destroy all objects to free memory

    Set strComputer = Nothing

    Set objWMIService = Nothing

    Set FileSystemObject = Nothing

    Set Drives= Nothing

    Function Run (ByVal cmd)

    Dim sh: Set sh = CreateObject("WScript.Shell")

    Dim wsx: Set wsx = Sh.Exec(cmd)

    If wsx.ProcessID = 0 And wsx.Status = 1 Then

    ' (The Win98 version of VBScript does not detect WshShell.Exec errors)

    Err.Raise vbObjectError,,"WshShell.Exec failed."

    End If


    Dim Status: Status = wsx.Status

    'WScript.StdOut.Write wsx.StdOut.ReadAll()

    'WScript.StdErr.Write wsx.StdErr.ReadAll()

    If Status <> 0 Then Exit Do

    'WScript.Sleep 10


    Run = wsx.ExitCode

    Set wsx = Nothing

    Set sh = Nothing

    End Function

    ' Runs an internal command interpreter command.

    Function RunCmd (ByVal cmd)

    RunCmd = Run("%ComSpec% /s /c " & cmd)

    End Function

  • P.S. -- of course, there are additional steps to filter the data out, but looking for "like %drive %" and a substring will give you the drive letter, and "like '% bytes free%" will get the free space.

    This gives you the free space but not the total space, but if you are in a restricted environment with little write access, it's an alternative.

Viewing 15 posts - 1 through 15 (of 18 total)

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