[marconi]

I have 3 jobservers and a database server.

1) I need to first check the disk space.
2) then check if the free space on each drive is greater than 15% of the
total disk space(which is the threshold value) and notify the user.
3) then shrink the databases.

I have written the following VBSCript, but it is not serving the purpose.
Request you to please provide me with the correct inputs/script.

-------------------------------------------------------------------------------------

Set iFSO = CreateObject("Scripting.FilesyStemObject") 
 Set oFSO = CreateObject("Scripting.FilesyStemObject") 
 InputFile="Diskspace.txt" 
 Outputfile="Diskspacelist.csv"  
 Set ofile = ofso.createTextFile(OutputFile) 
 Set ifile = ifSO.OpenTextFile(inputfile) 
 Const GBCONVERSION= 1048576000 
 ofile.writeline "Server,Drive,Disk Size,FreeSpace" 
 Do until ifile.AtEndOfLine 
                 Server = ifile.ReadLine                 
                 Set objWMIService = GetObject("winmgmts://" & Server) 
                 Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk") 
                 For Each objLogicalDisk In colLogicalDisk 
                                 if objLogicalDisk.drivetype=3 then 
                                                 ofile.writeline Server & "," & objLogicalDisk.DeviceID &_ 
                                        "," & FormatNumber(objLogicalDisk.size/GBCONVERSION,2) & "GB, " &_ 
                                        FormatNumber(objLogicalDisk.freespace/GBCONVERSION,2) & "GB, " 
                                 end if 
        Next               
 Loop 
 
 iThreshold=(15/100)*(size)
 iSize=freespace
 If Int(iSize) < Int(iThreshold) Then
 'free space is less than the threshold so generate an alert
 wscript.Echo "Alert!"
 strDescription=objLogicalDisk.freespace & " is less than the specified threshold of " &_
 FormatNumber(iThreshold,2,,True) & " GB. Free space is " &_
 FormatNumber(iSize,2,,True) & " GB"
 wscript.echo strDescription
 Else
 'folder size is OK
 WScript.Echo "The size of " &freespace & " (" & iSize &_
  ") is at least the threshold of " & iThreshold
 End If

-------------------------------------------------------------------------------------------

Thanks a lot in advance.

Marconi.

[pradeep]

What exactly is the problem you are facing with this script?

[marconi]

Hi,

My implementation of the Logic for checking the threshold is incorrect. I am getting Syntax Errors. Could you please provide your inputs.

Also could you please provide your inputs for shrinking the database wherein I am creating a .cmd file with the following code :-
--------------------------------------------------------------------------------------------------------
sqlcmd -s -Q –o switch "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (jobserver1, 10)', @replacechar = 'jobserver1'" -c go



sqlcmd -s -Q –o switch "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (jobserver2, 10)', @replacechar = 'jobserver2'" -c go



sqlcmd -s -Q –o switch "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (jobserver3, 10)', @replacechar = 'jobserver2'" -c go



sqlcmd -s -Q –o switch "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (databaseserver, 10)', @replacechar = 'databaseserver'" -c go

--------------------------------------------------------------------------------------------------------

When this CMD File is run, the databases on each server need to be shrunk and the new output details will be redirected to a log/txt file.

Thanks and Regards,
Marconi.