February 20, 2008 at 9:47 pm
Comments posted to this topic are about the item Improved Drive Space Monitoring
February 21, 2008 at 4:28 am
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
February 21, 2008 at 8:00 am
what is the purpose of replacing xp_fixeddrives with a call to xp_cmdshell. I would prefer to have xp_cmdshell disabled.
February 21, 2008 at 8:59 am
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..
February 21, 2008 at 9:07 am
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?
February 21, 2008 at 10:18 am
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.
February 21, 2008 at 10:31 am
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...
February 21, 2008 at 11:02 am
Great! Glad the script is running on your 64 bit system.
Can you share the code that handles CDROM??:)
February 21, 2008 at 11:38 am
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
February 21, 2008 at 12:04 pm
Thanks. We will look at adding this because CDROM Drives do not interest us at all 🙂
February 21, 2008 at 7:32 pm
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.
February 21, 2008 at 8:33 pm
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
February 22, 2008 at 7:02 am
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.
May 22, 2008 at 8:04 am
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
May 22, 2008 at 8:07 am
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 17 total)
You must be logged in to reply to this topic. Login to reply