SQL Database Get Data/Log Space Info

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

SQL Database Get Data/Log Space Info

Logged in as: Guest

arrSession:exec spGetSession 2,16,49102

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> Post a VBScript >> SQL Database Get Data/Log Space Info
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

SQL Database Get Data/Log Space Info - 7/6/2007 12:11:20 AM

4scriptmoni

Posts: 208
Score: 0
Joined: 5/3/2007
Status: offline

To get the Space Allocated by the SQL Server of a Database.

'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::: 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;Data Source=TSTDSV01\I1;" & _
          "Trusted_Connection=Yes;Initial Catalog=Master;" & _
               "User ID=tst\_c23;Password=;1x3t231p"
   
        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;Data Source=TSTDasd1V01\I1;" & _
          "Trusted_Connection=Yes;Initial Catalog=Master;" & _
               "User ID=tst\_cd;Password=;1asdmp"
   
        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

_____________________________

Enterprise Microsoft Scripts
Exchange, Login/Logout Monitor,TS, Monitoring, Security, AD, etc...
http://www.felipeferreira.net