Shulou(Shulou.com)06/03 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.