Improved Drive Space Monitoring

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Mark Nash » Improved Drive Space Monitoring

18 posts, Page 2 of 2

««1 2

Improved Drive Space Monitoring

Rate Topic

Display Mode

Topic Options

Author

Message

einman33

Posted Thursday, February 21, 2008 7:32 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512

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.

Post #458895

Adiga

Posted Thursday, February 21, 2008 8:33 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 4:09 AM
Points: 1,618, Visits: 20,904

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

Post #458902

markenash

Posted Friday, February 22, 2008 7:02 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:36 AM
Points: 23, Visits: 115

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.

Post #459089

michael.henry.bah

Posted Thursday, May 22, 2008 8:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 10:39 AM
Points: 3, Visits: 91

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

Post #505189

michael.henry.bah

Posted Thursday, May 22, 2008 8:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 10:39 AM
Points: 3, Visits: 91

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.

Post #505190

markenash

Posted Friday, May 30, 2008 9:14 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:36 AM
Points: 23, Visits: 115

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

Post #509185

pmcpherson

Posted Tuesday, June 24, 2008 11:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:07 AM
Points: 40, Visits: 349

Pradeep, Does this help you?

declare @cmd varchar(2000)
declare @Name varchar(100)
declare @reterror int

Set @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 @cmd

If @RETERROR <> 0
Begin
--You know the rest here
Select getdate()
End

Don't forget to uncomment the Execute.

Post #522749

michael.henry.bah

Posted Tuesday, June 24, 2008 4:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 10:39 AM
Points: 3, Visits: 91

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.

Post #522935

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions