SQLServerCentral / Discuss Content Posted by Mark Nash / Article Discussions / Article Discussions by Author / Improved Drive Space Monitoring / Latest Posts

RE: Improved Drive Space Monitoring

michael.henry.bah — Tue, 24 Jun 2008 16:21:36 GMT

Grasshopper,Sorry for the delayed response, I have been busy with a "not" vacation (swamped at work). I tried a few methods and the only way I could determine total space in DOS was to use chkdsk c: /c /i, but this is a very slow and disk-intensive process just to get free space... diskpart would work if it did not require a temporary file for scripting input -- why couldn't they just make it accept the script on the command line, like ntdsutil or iisutil?Anyway, the answer is yes it can be done, but with a lot of slow, heavy drive activity. We're back to ActiveX scripting of WMI to efficiently get total space.

RE: Improved Drive Space Monitoring

pmcpherson — Tue, 24 Jun 2008 11:07:59 GMT

Pradeep, Does this help you?declare @cmd varchar(2000)declare @Name varchar(100)declare @reterror intSet @Name = 'Pradeep.vbs'set @cmd = 'bcp "Select ''First Line Here'' Union All '+ 'Select ''Second Line Here'' Union All '+ 'Select ''ABC'' Union All '+ 'Select ''123'''+' " queryout C:\' + @name +' -b5000 -c -t"" -T'print @cmd--Select len(@cmd)--execute @RETERROR = master..xp_cmdshell @cmdIf @RETERROR <> 0Begin --You know the rest here Select getdate()EndDon't forget to uncomment the Execute.

RE: Improved Drive Space Monitoring

markenash — Fri, 30 May 2008 09:14:18 GMT

Good morning Michael, I've been on Vacation. Your solution is very interesting! Never thought of using 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');"""""Do you think this could be expanded to also return total space??

RE: Improved Drive Space Monitoring

michael.henry.bah — Thu, 22 May 2008 08:07:09 GMT

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.

RE: Improved Drive Space Monitoring

michael.henry.bah — Thu, 22 May 2008 08:04:49 GMT

What do you all think about this method? No intermediate files needed!' ' Capturues Free and Percent Free Space for all local drives.' ' ********************************************************************************Const ForAppending = 8strComputer = "."Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")Set FileSystemObject = CreateObject("Scripting.FileSystemObject")'''''''''''''''''''''' Total and Free Space CaptureSet Drives = FileSystemObject.DrivesFor 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\BinnRunCmd """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 IFNext'' Destroy all objects to free memorySet strComputer = NothingSet objWMIService = Nothing Set FileSystemObject = NothingSet 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.ExitCodeSet wsx = NothingSet sh = Nothing End Function' Runs an internal command interpreter command.Function RunCmd (ByVal cmd) RunCmd = Run("%ComSpec% /s /c " & cmd) End Function

RE: Improved Drive Space Monitoring

markenash — Fri, 22 Feb 2008 07:02:46 GMT

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.

RE: Improved Drive Space Monitoring

Adiga — Thu, 21 Feb 2008 20:33:10 GMT

[i]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.[/i]Somebody pls help me with the above request.

RE: Improved Drive Space Monitoring

einman33 — Thu, 21 Feb 2008 19:32:46 GMT

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.

RE: Improved Drive Space Monitoring

markenash — Thu, 21 Feb 2008 12:04:14 GMT

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

RE: Improved Drive Space Monitoring

john.danley — Thu, 21 Feb 2008 11:38:28 GMT

Of course! Actually I cheated originally and just added [b]AND DiskDrive.DriveLetter <> "D"[/b] in the For Each loop. To make the code independent of each machine's actual setup I changed this to [b]If DiskDrive.IsReady AND DiskDrive.DriveType <> 4 Then[/b]. 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 [b]If DiskDrive.IsReady AND DiskDrive.DriveType = 2 Then[/b]. This site provided good info on the subject: http://www.brettb.com/VBScriptDrivesCollection.asp

RE: Improved Drive Space Monitoring

markenash — Thu, 21 Feb 2008 11:02:59 GMT

Great! Glad the script is running on your 64 bit system.Can you share the code that handles CDROM??:)

RE: Improved Drive Space Monitoring

john.danley — Thu, 21 Feb 2008 10:31:32 GMT

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

RE: Improved Drive Space Monitoring

markenash — Thu, 21 Feb 2008 10:18:45 GMT

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.

RE: Improved Drive Space Monitoring

john.danley — Thu, 21 Feb 2008 09:07:03 GMT

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?

RE: Improved Drive Space Monitoring

markenash — Thu, 21 Feb 2008 08:59:46 GMT

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

RE: Improved Drive Space Monitoring

Robert Hermsen — Thu, 21 Feb 2008 08:00:31 GMT

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

RE: Improved Drive Space Monitoring

Adiga — Thu, 21 Feb 2008 04:28:53 GMT

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

Improved Drive Space Monitoring

markenash — Wed, 20 Feb 2008 21:47:37 GMT

Comments posted to this topic are about the item [B]Improved Drive Space Monitoring[/B]