日志空间使用情况的脚本

获得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::: SERVERNAMEInstance, domainuser, 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