Improved Drive Space Monitoring

  • markenash

    Mr or Mrs. 500

    Points: 507

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

  • Adiga

    One Orange Chip

    Points: 27224

    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
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • BJ Hermsen

    Hall of Fame

    Points: 3123

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

  • markenash

    Mr or Mrs. 500

    Points: 507

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

  • john.danley

    SSC Eights!

    Points: 906

    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?

  • markenash

    Mr or Mrs. 500

    Points: 507

    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.

  • john.danley

    SSC Eights!

    Points: 906

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

  • markenash

    Mr or Mrs. 500

    Points: 507

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

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

  • john.danley

    SSC Eights!

    Points: 906

    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: http://www.brettb.com/VBScriptDrivesCollection.asp

  • markenash

    Mr or Mrs. 500

    Points: 507

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

  • einman33

    SSCrazy

    Points: 2753

    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.

  • Adiga

    One Orange Chip

    Points: 27224

    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
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • markenash

    Mr or Mrs. 500

    Points: 507

    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.

  • michael.henry.bah

    SSC Rookie

    Points: 43

    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

    Next

    '' 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

    Do

    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

    Loop

    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

  • michael.henry.bah

    SSC Rookie

    Points: 43

    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