SQL: Database Usage and Limits Reports

This script is very useful to create a report with a list of all databases and their size limit and actual usage.

There is also a total divided into DataFiles and Transaction Logs.


Set Con = CreateObject("ADODB.Connection")
Con.Open "Driver={SQL Server}; Server=SQL_SRV; Database=Master"
TotalDF = 0
TotalTL = 0
TotalDFUsed = 0
TotalTLUsed = 0
Num = 0
ListaFileSQL
wscript.echo "**********************************************************"
wscript.echo "Total Number of Databases: " & num
wscript.echo "Total Data Files: " & TotalDF/1024 & " MB - " & round(TotalDFUsed/1024,0) & " MB"
wscript.echo "Total Transaction Log: " & TotalTL/1024 & " MB - " & round(TotalTLUsed/1024,0) & " MB"
wscript.echo "**********************************************************"


Function ListaFileSQL
Set rs1 = CreateObject("ADODB.RecordSet")
rs1.ActiveConnection = Con
rs1.CursorType = adOpenStatic
rs1.PageSize = 1
sqlString1 = "SELECT name, cast(DataBasePropertyex([name], 'Recovery') as varchar) AS RecoveryMode FROM [Master].[dbo].sysdatabases"
sqlstring1 = sqlstring1 + " WHERE DATABASEPROPERTY(name, N'IsOffline')= 0 AND name NOT IN ('master', 'model', 'tempdb', 'msdb', 'northwind', 'pubs')"
'wscript.echo sqlstring1
rs1.Open sqlString1, Con, 3, 3
Do While Not rs1.EOF
num = num +1
'wscript.echo rs1("name") & " - " & rs1("RecoveryMode")
CheckDB rs1("name"), rs1("RecoveryMode")
rs1.MoveNext
Loop
End function

Function CheckDB(NomeDB, RecoveryMode)
Set rs2 = CreateObject("ADODB.RecordSet")
rs2.ActiveConnection = Con
rs2.CursorType = adOpenStatic
rs2.PageSize = 1
sqlString1 = "select convert(float,size) AS Actual_SIZE, convert(float,maxsize) AS MAX_SIZE, right(rtrim(filename),3) AS TIPO from [" & NomeDB & "].[DBO].sysfiles"
rs2.Open sqlString1, Con, 3, 3
Do While Not rs2.EOF
wscript.echo nomedb & " (" & rs2("TIPO") & ") : " & rs2("MAX_SIZE")*8/1024 & " MB - " & round(rs2("ACTUAL_SIZE")*8/1024,0) & " MB"
if UCase(rs2("TIPO")) = "MDF" then
TotalDF = TotalDF + rs2("MAX_SIZE")*8
TotalDFUsed = TotalDFUsed + rs2("ACTUAL_SIZE")*8
else
TotalTL = TotalTL + rs2("MAX_SIZE")*8
TotalTLUsed = TotalTLUsed + rs2("ACTUAL_SIZE")*8
end if
rs2.MoveNext
Loop
End function

Share on Google Plus

About Vittorio Pavesi