Getsqlinfo. vbs script for obtaining SQL data/log space usage

Obtain SQL data/log space usage, used and unused space scripts

Getsqlinfo. vbs
'Script to get SQL data/log space used, space unused,
And space free
'Author: Felipe Ferreira, Daniel magrini
'Date': 05/07/07
'Version 2, 0

'@ To change: servername \ instance, domain \ User, password and Databse!

'____________________________________________________________________________
Const forreading = 1, forwriting = 2, forappending = 8
Set ofso = Createobject ("scripting. FileSystemObject ")
Outputfile = "checksqldb_size.txt"
Set ofile = ofso. opentextfile (outputfile, 8, true)
Ofile. writeline "###################################### ################"
Ofile. writeline "this command executed in" & Date & "at" & time & vbcrlf
'____________________________________________________________________________

Checksqldata
Checksqllog

############## Get SQL data space used, space total, space free
'Function checksql (strserver, strdb) in the future make it a function ....
Sub checksqldata
Const adopendynamic = 1, adlockoptimistic = 3
Dim strquery
Dim objconnection, objrecordset
Dim strqueryresult, strqueryresult2
Dim useddataspace, totaldataspace, freedataspace
Set objconnection = Createobject ("ADODB. Connection ")
Set objrecordset = Createobject ("ADODB. recordset ")

Objconnection. Open _
"Provider = sqloledb.1; server = 192.168.8.10; user id = sa; Password = LCX; database = Master ;"

Strquery = "DBCC showfilestats"
Objrecordset. Open strquery, objconnection, adopendynamic, adlockoptimistic
If objrecordset. EOF then
'Nothing returned
Wscript. Echo "error !!! "
Else

'Note: To get the value in MB 64/1024 = 0.0625
Do until objrecordset. EOF
Strqueryresult = objrecordset. Fields ("usedextents ")
Useddataspace = strqueryresult * 0.0625
Strqueryresult2 = objrecordset. Fields ("totalextents ")
Totaldataspace = strqueryresult2 * 0.0625
Freedataspace = totaldataspace-useddataspace

'Clean data
Useddataspace = left (useddataspace, 4)
Freedataspace = left (freedataspace, 4)
Totaldataspace = left (totaldataspace, 4)

'Print result on screen
Wscript. Echo "used space (MB) =" & useddataspace
Wscript. Echo "Free Space (MB) =" & freedataspace
Wscript. Echo "total space (MB) =" & totaldataspace

'Write on file
Ofile. writeline "used data space (MB) =" & useddataspace
Ofile. writeline "free data space (MB) =" & freedataspace
Ofile. writeline "total data space (MB) =" & totaldataspace

Objrecordset. movenext
Loop
End if
Objrecordset. Close
Objconnection. Close
Set objconnection = nothing
Set objrecordset = nothing
End sub

Sub checksqllog
Const adopendynamic = 1, adlockoptimistic = 3
Dim strquery
Dim objconnection, objrecordset
Dim strqueryresult, strqueryresult2
Dim usedlogspace, totallogspace, freelogspace
Set objconnection = Createobject ("ADODB. Connection ")
Set objrecordset = Createobject ("ADODB. recordset ")

Objconnection. Open _
"Provider = sqloledb.1; server = 192.168.8.10; user id = sa; Password = LCX; database = Master ;"

Strquery = "DBCC sqlperf (logspace )"
Objrecordset. Open strquery, objconnection, adopendynamic, adlockoptimistic
If objrecordset. EOF then
'Nothing returned
Wscript. Echo "error !!! "
Else

Do until objrecordset. EOF
If objrecordset. Fields ("Database Name") = "master" then

Strqueryresult = objrecordset. Fields ("log size (MB )")
Strqueryresult2 = objrecordset. Fields ("log space used (% )")
Usedlogspace = (strqueryresult * strqueryresult2)/100
Totallogspace = strqueryresult
Freelogspace = totallogspace-usedlogspace

'Clean data
Usedlogspace = left (usedlogspace, 4)
Freelogspace = left (freelogspace, 4)
Totallogspace = left (totallogspace, 4)

'Print result on screen
Wscript. Echo "used space (MB) =" & usedlogspace
Wscript. Echo "Free Space (MB) =" & freelogspace
Wscript. Echo "total space (MB) =" & totallogspace

'Write on file
Ofile. writeline "used log space (MB) =" & usedlogspace
Ofile. writeline "free log space (MB) =" & freelogspace
Ofile. writeline "total log space (MB) =" & totallogspace

Ofile. Close

Exit do

End if

objrecordset. movenext
loop
end if
objrecordset. close
objconnection. close
set objconnection = nothing
set objrecordset = nothing
end sub
wscript. quit