Getsqlinfo.vbs script to obtain SQL data/log space usage _vbs
Source: Internet
Author: User
Scripting for SQL data/Log space usage, used and unused space
Getsqlinfo.vbs
' Script to get SQL data/log spaces Used, space unused,
and spaces 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 USED.
' 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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.