getSQLinfo.vbs 獲得SQL數據/日志空間使用情況的腳本
2019-10-26 18:01:27
供稿：網友
獲得SQL數據/日志空間使用，已使用的和未使用的空間的腳本
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
