Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Improved Drive Space Monitoring Expand / Collapse
Author
Message
Posted Thursday, February 21, 2008 7:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Thursday, February 21, 2008 8:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Posted Friday, February 22, 2008 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:43 PM
Points: 23, 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.



Post #459089
Posted Thursday, May 22, 2008 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, May 22, 2008 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, May 30, 2008 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:43 PM
Points: 23, 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??



Post #509185
Posted Tuesday, June 24, 2008 11:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:24 AM
Points: 56, Visits: 413
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
Posted Tuesday, June 24, 2008 4:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««12

Permissions Expand / Collapse