Technical Article

Find Db space used  using SQL-DMO

,

Use SQL-DMO to find database size used for more than one sql server in your enviroment.

'Find the DataFile usage for each database, for each SQL server in the text file
'Place name of SQLservers in the text file in Sqlservers.txt in c drive.
'Created by Ravi Pannem, Sprint
'=============================================================================
Dim oServer
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SQLSrvrs = FSO.OpenTextFile("C:\SqlServers.txt")


If FSO.FileExists("C:\SQLServers_Info.txt") then
FSO.DeleteFile "C:\SQLServers_Info.txt", True
End If

Set OutPutFile=FSO.OpenTextFile("C:\SQLServers_Info.txt",8,True)
outputfile.writeline "Name of database"&","&"DataSpace Usage (in MB)"&vbCrLf&"======================================"


dim arraylines()

do until SQLSrvrs.atendofstream = True
redim arraylines(i)
arraylines(i) = SQLsrvrs.readline
strComputer=arraylines(i)
call Find_Spaceused()
loop

Sub Find_Spaceused()

Set oServer=CreateObject("SQLDMO.Sqlserver")
oServer.LoginSecure=True
oServer.Connect(strComputer)

set oDatabases=oServer.Databases
outputfile.writeline oServer.NetName&vbCrlf&"========================================"
for each db in oDatabases
outputfile.writeline db.name&","&db.dataspaceusage/1000
next
outputfile.writeline "========================================" 
oServer.Disconnect

set oServer=Nothing

End Sub

msgbox "DONE"

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating