获得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