获得SQL数据/日志空间使用，已使用的和未使用的空间的脚本
getSQLinfo.vbs
'scripttogetSQLDATA/LOGSpaceUsed,Spaceunused,
andSpaceFree
'Author:FelipeFerreira,DanielMagrini
'Date:05/07/07
'Version2,0
'@@TOCHANGE:::SERVERNAMEInstance,domainuser,passwordANDDATABSE!
'____________________________________________________________________________
ConstForReading=1,ForWriting=2,ForAppending=8
SetoFSO=CreateObject("Scripting.FilesyStemObject")
outputfile="CheckSqlDB_Size.txt"
Setofile=oFso.OpenTextFile(outputfile,8,True)
oFile.Writeline"######################################################"
oFile.Writeline"Thiscommandexecutedin"&Date&"at"&Time&VbCrLf
'____________________________________________________________________________
CheckSQLData
CheckSQLLOG
'##############GETSQLDATASPACEUSED,SPACETOTAL,SPACEFREE
'FunctioncheckSQL(strServer,strDB)inthefuturemakeitafunction....
SubCheckSQLDATA
ConstadOpenDynamic=1,adLockOptimistic=3
DimstrQuery
DimobjConnection,objRecordSet
DimstrQueryResult,strQueryResult2
DimUsedDataSpace,TotalDataSpace,FreeDataSpace
SetobjConnection=CreateObject("ADODB.Connection")
SetobjRecordSet=CreateObject("ADODB.Recordset")
objConnection.Open_
"Provider=SQLOLEDB.1;Server=192.168.8.10;UserID=sa;Password=lcx;Database=master;"
strQuery="DBCCshowfilestats"
objRecordSet.OpenstrQuery,objConnection,adOpenDynamic,adLockOptimistic
ifobjRecordSet.eofThen
'nothingreturned
wscript.echo"ERROR!!!"
Else
'NOTE:TogetthevalueinMB64/1024=0.0625
DoUntilobjRecordSet.eof
strQueryResult=objRecordSet.Fields("UsedExtents")
UsedDataSpace=strQueryResult*0.0625
strQueryResult2=objRecordSet.Fields("TotalExtents")
TotalDataSpace=strQueryResult2*0.0625
FreeDataSpace=TotalDataSpace-UsedDataSpace
'CleanData
UsedDataSpace=Left(UsedDataSpace,4)
FreeDataSpace=Left(FreeDataSpace,4)
TotalDataSpace=Left(TotalDataSpace,4)
'PrintResultonScreen
Wscript.echo"UsedSpace(MB)="&UsedDataSpace
Wscript.Echo"FreeSpace(MB)="&FreeDataSpace
Wscript.Echo"TotalSpace(MB)="&TotalDataSpace
'WriteonFile
ofile.WriteLine"UsedDATASpace(MB)="&UsedDataSpace
ofile.WriteLine"FreeDATASpace(MB)="&FreeDataSpace
ofile.WriteLine"TotalDATASpace(MB)="&TotalDataSpace
objRecordSet.MoveNext
loop
endif
objRecordSet.Close
objConnection.Close
setobjConnection=nothing
setobjRecordSet=nothing
endsub
SubCheckSQLLOG
ConstadOpenDynamic=1,adLockOptimistic=3
DimstrQuery
DimobjConnection,objRecordSet
DimstrQueryResult,strQueryResult2
DimUsedLogSpace,TotalLogSpace,FreeLogSpace
SetobjConnection=CreateObject("ADODB.Connection")
SetobjRecordSet=CreateObject("ADODB.Recordset")
objConnection.Open_
"Provider=SQLOLEDB.1;Server=192.168.8.10;UserID=sa;Password=lcx;Database=master;"
strQuery="DBCCSQLPERF(LOGSPACE)"
objRecordSet.OpenstrQuery,objConnection,adOpenDynamic,adLockOptimistic
ifobjRecordSet.eofThen
'nothingreturned
wscript.echo"ERROR!!!"
Else
DoUntilobjRecordSet.eof
IfobjRecordSet.Fields("DatabaseName")="master"Then
strQueryResult=objRecordSet.Fields("LogSize(MB)")
strQueryResult2=objRecordSet.Fields("LogSpaceUSed(%)")
UsedLogSpace=(strQueryResult*strQueryResult2)/100
TotalLogSpace=strQueryResult
FreeLogSpace=TotalLogSpace-UsedLogSpace
'CleanData
UsedLogSpace=Left(UsedLogSpace,4)
FreeLogSpace=Left(FreeLogSpace,4)
TotalLogSpace=Left(TotalLogSpace,4)
'PrintResultonScreen
Wscript.echo"UsedSpace(MB)="&UsedLogSpace
Wscript.Echo"FreeSpace(MB)="&FreeLogSpace
Wscript.Echo"TotalSpace(MB)="&TotalLogSpace
'WriteonFile
oFile.WriteLine"UsedLOGSpace(MB)="&UsedLogSpace
oFile.WriteLine"FreeLOGSpace(MB)="&FreeLogSpace
oFile.WriteLine"TotalLOGSpace(MB)="&TotalLogSpace
oFile.close
ExitDo
EndIf
objRecordSet.MoveNext
loop
endif
objRecordSet.Close
objConnection.Close
setobjConnection=nothing
setobjRecordSet=nothing
endsub
WSCript.Quit
【getSQLinfo.vbs 获得SQL数据/日志空间使用情况的脚本】相关文章：