How does getSQLinfo.vbs get SQL data / Log Space usage
This article is about how getSQLinfo.vbs obtains SQL data / log space usage. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
Scripts to get SQL data / log space usage, used and unused space
GetSQLinfo.vbs
'script to get SQL DATA/LOG Space Used, Space unused
And Space Free
'Author: Felipe Ferreira, Daniel Magrini
'Date: 05Universe 07Universe 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 "errology thanks!"
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 "errology thanks!"
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
Thank you for reading! This is the end of this article on "how getSQLinfo.vbs obtains SQL data / log space usage". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!
