Check Free Space of Drive

[alanimal]

Hi Guys,

I have a drive for log files on an SQL server that now and then fill up.

How can I check that the drive as sufficient room, and if not, the script automatically restarts the MSSQLSERVER service? (this clears out the TempDB log files).

Thanks in advance
AL

[Rain]

Is this what you looking for?

Code: Select all

VBSTART

Function FreeSpace(drvPath)
  Dim fso, drv, s
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set drv = fso.GetDrive(fso.GetDriveName(drvPath))
  FreeSpace = drv.FreeSpace
End Function

VBEND

let>DIRECTORY=C:\
VBEval>FreeSpace("%DIRECTORY%"),fs
MessageModal>Free Space on Drive %DIRECTORY%: %fs% bytes

[JRL]

Or, straight out of help for libfunc>

Code: Select all

LibFunc>kernel32,GetDiskFreeSpaceExA,spacefree,c:\,REF:0,REF:0,REF:0
Let>used=spacefree_3/1024
Let>free=spacefree_4/1024
MessageModal> Disk space used: %used%Kb %CRLF% Free space: %free%Kb

Edit:Somehow in a copy and paste from help, a percent sign disappeared.
I put it back.

[alanimal]

Thanks. Rain, yours works sweet as - but I get an error on JRL's post.

Rain, how can I get the output to be in Gb? instead of KB?

Thanks!

[Me_again]

You probably will want to round the result too, so to get the result in gigs add this line after the VBEval>FreeSpace etc. line

VBEval>Round(%fs%/1073741824,4),fs

The ",4" rounds to 4 decimals, you can change that to your choice.

[Rain]

Here are the conversions from bytes to Kilobyte, Megabyte, Gigabyte, Terabyte and Petabyte in "plain" English I hope.

Code: Select all

VBSTART

Function FreeSpace(drvPath)
  Dim fso, drv, s
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set drv = fso.GetDrive(fso.GetDriveName(drvPath))
  FreeSpace = drv.FreeSpace
End Function

VBEND

let>DIRECTORY=C:\
VBEval>FreeSpace("%DIRECTORY%"),BYTES_RESULT

VBEval>Round(%BYTES_RESULT%/1024,2),KB_RESULT
VBEval>Round(%BYTES_RESULT%/1024/1024,2),MB_RESULT
VBEval>Round(%BYTES_RESULT%/1024/1024/1024,2),GB_RESULT
VBEval>Round(%BYTES_RESULT%/1024/1024/1024/1024,10),TB_RESULT
VBEval>Round(%BYTES_RESULT%/1024/1024/1024/1024/1024,10),PB_RESULT
MDL>%BYTES_RESULT% Bytes%CRLF%%KB_RESULT% Kilobytes%crlf%%MB_RESULT% Megabytes%CRLF%%GB_RESULT% Gigabytes%CRLF%%TB_RESULT% Terabytes%CRLF%%PB_RESULT% Petabytes

[alanimal]

Cheers Guys,

All working a treat now - this was the final code:

Code: Select all

VBSTART

Function FreeSpace(drvPath)
		 Dim fso, drv, s
		 Set fso = CreateObject("Scripting.FileSystemObject")
		 Set drv = fso.GetDrive(fso.GetDriveName(drvPath))
		 FreeSpace = drv.FreeSpace
End Function

VBEND

let>DIRECTORY=F:\
VBEval>FreeSpace("%DIRECTORY%"),fs
VBEval>Round(%fs%/1073741824,4),fs

//Check that the DiskSpace is more than 10GB to be safe

Let>GB=10

IF>{%fs%>%GB%}
MessageModal>Free Space On Drive %DIRECTORY%: %fs% GB OK
GoTo>END
Else
GoTo>Service

EndIF

Label>Service
//Restart the services
VBSTART

Sub StopService(sServiceName)
	strComputer = "."
  	Set wbemServices = GetObject("winmgmts:\\" & strComputer)
	sWQL = "Select state from Win32_Service " & "Where displayname='" & sServiceName & "'"
	Set oResults = wbemServices.ExecQuery(sWQL)
	For Each oService In oResults
  		If Trim(LCase(oService.State)) = LCase("Running") Then
    	   oService.StopService
  		End If
	Next
End Sub

Sub StopService(sServiceName)
	strComputer = "."
  	Set wbemServices = GetObject("winmgmts:\\" & strComputer)
	sWQL = "Select state from Win32_Service " & "Where displayname='" & sServiceName & "'"
	Set oResults = wbemServices.ExecQuery(sWQL)
	For Each oService In oResults
  		If Trim(LCase(oService.State)) = LCase("Running") Then
    	   oService.StopService
  		End If
	Next
End Sub

Sub StartService(sServiceName)
	strComputer = "."
  	Set wbemServices = GetObject("winmgmts:\\" & strComputer)
	sWQL = "Select state from Win32_Service " & "Where displayname='" & sServiceName & "'"
	Set oResults = wbemServices.ExecQuery(sWQL)
	For Each oService In oResults
  		If Trim(LCase(oService.State)) = LCase("Stopped") Then
    	   oService.StartService
  		End If
	Next
End Sub

Sub StartService(sServiceName)
	strComputer = "."
  	Set wbemServices = GetObject("winmgmts:\\" & strComputer)
	sWQL = "Select state from Win32_Service " & "Where displayname='" & sServiceName & "'"
	Set oResults = wbemServices.ExecQuery(sWQL)
	For Each oService In oResults
  		If Trim(LCase(oService.State)) = LCase("Stopped") Then
    	   oService.StartService
  		End If
	Next
End Sub

VBEND
VBRun>StopService,SQLSERVERAGENT
Wait>10
VBRun>StopService,MSSQLSERVER
Wait>15
VBRun>StartService,MSSQLSERVER
Wait>10
VBRun>StartService,SQLSERVERAGENT

Let>SENDMAIL_STATUS=1
Let>subject=SQL Server Log Drive Error
Let>[email protected]
Let>myname=AlanMM
Let>[email protected];
Let>body=The LOG drive on SQL Server has ran out of space. %CRLF% Restarting SQL services to clear log files now.%CRLF% There is %fs% GB free Space.
SMTPSendMail>recipients,mail.chsglobal.com,me,myname,subject,body,
MessageModal>There is insufficient Free Space on Drive is %DIRECTORY%: %fs% GB BAD!!%crlf%restarting services now.

Label>END