SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improved Drive Space Monitoring


Improved Drive Space Monitoring

Author
Message
einman33
einman33
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 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.
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2660 Visits: 21012
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
markenash
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 129
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
michael.henry.bah
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
michael.henry.bah
michael.henry.bah
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
markenash
markenash
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 129
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??



pmcpherson
pmcpherson
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 460
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.
michael.henry.bah
michael.henry.bah
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search