SQL Server DB Size Report

This script is similar in style to my previous script SQL Server Job Status Report, but this script generates an HTML report on the size of all databases on a specific SQL Server instance. The report consists of several sections: a summary report on the size of all databases; a summary report on the size of all data files; a report on the size of all disks; details reports on each database.

The script is designed to be included in a DTS package ActiveX Task component and makes use of a number of DTS Package Global Variables - these variables are listed in detail in the script header.

The server from which the script is executed requires ADO to be installed, and also Outlook if the email option is to be used - I personally run this script from a SQL Server instance on my desktop PC. The script also makes use of the File System Object to gather data about the server disks, if the user executing the script does not have permissions on any of the server disks, follow the instructions in the script header on how to disable this feature.

This script has been tested against SQL Server 2000 running on NT and Win2K.

'********************************************************************************************************** ' Name: SQL Server DB Size Report ' Script Language: VBScript ' Author: Dave Leathem ' Date: 28th Nov 2003 ' ' Description: ' This script will generate an HTML report on the size of all databases on a specified SQL ' Server instance. This report can be emailed to specified addresses if required, or the ' generated HTML file may be published to a web site or distributed with any other file ' distribution mechanisms. ' The report is divided into four sections: DB Summary Report, which contains a table of ' current database sizes; Data File Summary Report, which contains a table of data file ' information, including size, growth and free space left on the relevant drive; Disk Space ' Summary Report, which contains a table of all available drives, free space, size and ' which data files reside on that drive; and finally, DB Detail Reports, which contains ' detailed reports on each database on the instance. ' In the DB Summary Report, databases are highlighted when their size exceeds the limit ' specified in the FILE_SIZE_LIMIT global variable. In the DB Detail Reports, the ' database size and/or the file sizes are highlighted when they exceed this limit. ' In the Data File Summary Report, files are highlighted when their growth value exceeds ' the amount of remaining free space on the relevant drive. ' ' Package Global Variables: ' This script requires a number of Global Variables to be created in any package that it is ' included as part of. ' Varible NameReq'd/OptDescription ' ---------------------------------------------------------------------------------------------------------------------------------------- ' TARGET_SERVERRequiredSpecifies the SQL Server instance for which the 'size report will be compiled. ' ADDRESSESOptionalContains a list of email addresses to which the 'report will be sent. Addresses must be separated 'by semi-colons, and the list must be terminated 'with a semi-colon. ' WIN_SECURITYRequiredSpecifies whether to use Windows Integrated 'Security when connecting to the specified SQL 'Server instance. Valid values are "YES" and '"NO". ' UIDOptionalOnly required if Windows Integrated Security is 'not being used. Specifies a valid SQL Server 'user ID. ' PWDOptionalOnly required if Windows Integrated Security is 'not being used. Specifies the password for the 'SQL Server user ID. ' FILE_SIZE_LIMITRequiredSpecifies a warning size limit for the database 'and data files. ' EMAIL_REPORTRequiredSpecifies if the job report is to be emailed. Valid 'values are "YES" and "NO". ' REPORT_PATHRequiredSpecifies the file path where the job report will 'be written to. ' KEEP_REPORT_FILERequiredSpecifies if the Report file is to be retained when 'the report is generated. Valid values are "YES" 'and "NO". ' DISABLE_DISK_SIZERequiredSpecifies if the job should calculate the size of 'the server drives. This should be set to "NO" if 'the user id running the job does not have the 'Windows permissions to access all the drives. 'Valid values are "YES" and "NO". ' '--------------------------------------------------------------------------------------------------------------------------------------------- ' Revision History '--------------------------------------------------------------------------------------------------------------------------------------------- ' DateVersionComment ' ------------------------------------------------------------------------------------------------------------------------------------- ' 04 Dec 20031.0Initial version ' 20 Jan 20042.0HTML report now displays files sizes graphically. ' '********************************************************************************************************** '********************************************************************************************************** ' Function:Main ' Description:Main processing function for ActiveX script. This function should be 'defined as the "Entry function" for the ActiveX Script Task. '********************************************************************************************************** Function Main() Dim MailSubject, MailText, HTMLText, FileRepText, DiskRepText, DBDetailRep Dim ErrMsg, ErrLineNo, LineCount, ErrLine Dim DBId, FileSize, DBName, LocalPath, intDBSize, intFileSize, intGrowth Dim DBFileList(10,2), intArr, flgNew, DiskDetail(100), DiskLetter(100), intDisk, FileDetail, DiskSpace(100), DiskSize(100) Dim PercOne(100), PercSpace, PercUsed, PercExt ' Declare ADO object variables Dim adoConn, rsDBSize, rsDiskSpace, rsDBDetail, rsDiskSize, sqlDiskSize, rsDBFiles ' Declare FSO variables DIM oFSO, OutFile, ReadFile ' Declare Outlook object variables Dim olApp, olMail, olNamespace ' Create ADO objects set adoConn = CreateObject("ADODB.Connection") set rsDBSize = CreateObject("ADODB.RecordSet") set rsDBFiles = CreateObject("ADODB.RecordSet") set rsDBDetail = CreateObject("ADODB.RecordSet") set rsDiskSpace = CreateObject("ADODB.RecordSet") set rsDiskSize = CreateObject("ADODB.RecordSet") sqlDiskSize = "DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT, @dletter VARCHAR(2) " & vbCrLf & _ "SET @dletter = '<drive>'" & vbCrLf & _ "EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT" & vbCrLf & _ "EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter" & vbCrLf & _ "EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT" & vbCrLf & _ "EXEC master.dbo.sp_OADestroy @oDrive" & vbCrLf & _ "EXEC master.dbo.sp_OADestroy @oFSO" & vbCrLf & _ "SELECT @dletter, @drsize" ' Open ADO connection using windows security IF DTSGlobalVariables("WIN_SECURITY").Value = "YES" THEN adoConn.Open = "Provider=SQLOLEDB;Integrated Security=SSPI;Server=" & DTSGlobalVariables("TARGET_SERVER").Value & ";Database=MSDB;Application Name=DB Size Report" ELSE adoConn.Open = "Provider=SQLOLEDB;UID=" & DTSGlobalVariables("UID").Value & ";PWD=" & DTSGlobalVariables("PWD").Value & ";Server=" & DTSGlobalVariables("TARGET_SERVER").Value & ";Database=MSDB;Application Name=DB Size Report" END IF ' Create FSO objects Set oFSO = CreateObject("Scripting.FileSystemObject") Set OutFile = oFSO.CreateTextFile(DTSGlobalVariables("REPORT_PATH").Value,true) OutFile.WriteLine("<HTML>") OutFile.WriteLine("<HEAD>") OutFile.WriteLine("<TITLE>DB Size Report for " & DTSGlobalVariables("TARGET_SERVER").Value & " on " & Date & " " & Time & "</TITLE>") OutFile.WriteLine("</HEAD>") OutFile.WriteLine("<BODY>") OutFile.WriteLine("DB Size Report for " & DTSGlobalVariables("TARGET_SERVER").Value & " on " & Date & " " & Time & "") OutFile.WriteLine(" <HR>") 'Create Outlook objects If DTSGlobalVariables("EMAIL_REPORT").Value = "YES" Then Set olApp = CreateObject("Outlook.Application") Set olNameSpace = olApp.GetNameSpace("MAPI") olNameSpace.Logon Set olMail = olApp.CreateItem(0) End If ' Open recordsets rsDBSize.Open "exec master.dbo.sp_databases", adoConn rsDiskSpace.Open "exec master.dbo.xp_fixeddrives", adoConn ' Write Report Index OutFile.WriteLine("<A NAME=""ReportIndex"">") OutFile.WriteLine("Report Index ") OutFile.WriteLine("<A HREF=""#disksumm"">Disk Space Summary Report<A> ") OutFile.WriteLine("<A HREF=""#diskfile"">Disk Space and Datafiles Reports<A> ") OutFile.WriteLine(" <HR>") ' Write Disk Space Summary Report OutFile.WriteLine("<A NAME=""disksumm"">") OutFile.WriteLine("Disk Space Summary Report ") OutFile.WriteLine("<TABLE BORDER=1 WIDTH=100%>") OutFile.WriteLine("<TR BGCOLOR=""NAVY""><TD>Drive</TD>") OutFile.WriteLine("<TD WIDTH=100>Total Size</TD>") OutFile.WriteLine("<TD WIDTH=100>Free Space</TD>") OutFile.WriteLine("<TD WIDTH=50%>% Disk Space Used</TD></TR>") rsDiskSpace.MoveFirst intDisk = 1 While Not rsDiskSpace.EOF OutFile.WriteLine("<TR><TD><A HREF=""#" & rsDiskSpace.Fields.Item(0) & "Drive"">" & rsDiskSpace.Fields.Item(0) & "</A></TD>") ' Write Disk Detail Heading DiskLetter(intDisk) = rsDiskSpace.Fields.Item(0) DiskDetail(intDisk) = "<A NAME=""#" & rsDiskSpace.Fields.Item(0) & "Drive"">" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TABLE BORDER=1 WIDTH=""100%"">" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TR><TD WIDTH=100 BGCOLOR=""navy"">Drive</TD>" DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD>" & rsDiskSpace.Fields.Item(0) & "</TD></TR>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "</TABLE>" & vbCrLf ' Write Disk Detail Summary DiskDetail(intDisk) = DiskDetail(intDisk) & "<TABLE BORDER=1 WIDTH=""100%""><TR>" & vbCrLf If DTSGlobalVariables("DISABLE_DISK_SIZE").Value = "NO" Then rsDiskSize.Open Replace(sqlDiskSize,"<drive>",rsDiskSpace.Fields.Item(0) & ":"), adoConn OutFile.WriteLine("<TD ALIGN=""RIGHT"">" & CStr(Round((rsDiskSize.Fields.Item(1) / 1024) / 1024, 0)) & " MB</TD>") DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">Total Size</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">" & CStr(Round((rsDiskSize.Fields.Item(1) / 1024) / 1024, 0)) & " MB</TD>" & vbCrLf PercOne(intDisk) = Round((rsDiskSize.Fields.Item(1) / 1024) / 1024, 2) / 100 DiskSpace(intDisk) = Round((rsDiskSize.Fields.Item(1) / 1024) / 1024, 0) - rsDiskSpace.Fields.Item(1) rsDiskSize.Close Else OutFile.WriteLine("<TD>[Function Disabled]</TD>") DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">Total Size</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">[Function disabled]</TD>" & vbCrLf End If OutFile.WriteLine("<TD ALIGN=""RIGHT"">" & rsDiskSpace.Fields.Item(1) & " MB</TD>") DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">Free Space</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">" & rsDiskSpace.Fields.Item(1) & " MB</TD>" & vbCrLf If DTSGlobalVariables("DISABLE_DISK_SIZE").Value = "NO" Then PercSpace =Round(rsDiskSpace.Fields.Item(1) / PercOne(intDisk), 0) PercUsed = 100 - PercSpace OutFile.WriteLine("<TD>") OutFile.WriteLine("<TABLE WIDTH=""100%"" BORDER=0 CELLSPACING=0>") OutFile.WriteLine("<TR><TD WIDTH=""" & PercUsed & "%"" BGCOLOR=""red"">X</TD>") OutFile.WriteLine("<TD WIDTH=""" & PercSpace & "%"" BGCOLOR=""green""></TD></TR>") OutFile.WriteLine("</TABLE>") OutFile.WriteLine("</TD>") DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">% Used</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">" & CStr(PercUsed) & "%</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">% Free</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">" & CStr(PercSpace) & " %</TD>" & vbCrLf Else OutFile.WriteLine("<TD>Function Disabled</TD>") DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">% Used</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">[Function disabled]</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100 BGCOLOR=""navy"">% Free</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""14%"">[Function disabled]</TD>" & vbCrLf End If OutFile.WriteLine("</TR>") DiskDetail(intDisk) = DiskDetail(intDisk) & "</TR></TABLE>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TABLE WIDTH=""100%"" BORDER=1 CELLSPACING=1>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TR BGCOLOR=""navy"">" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD>Database File</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=100>Database</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=55>Size</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=55>Ext.</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""50%"">% Disk Space Used</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "</TR><TR>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD>[Free Space] </TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD></TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD ALIGN=""RIGHT"">" & rsDiskSpace.Fields.Item(1) & " MB</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD></TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD>" & vbCrLf If DTSGlobalVariables("DISABLE_DISK_SIZE").Value = "NO" Then DiskDetail(intDisk) = DiskDetail(intDisk) & "<TABLE WIDTH=""100%"" BORDER=0 CELLSPACING=0>" & vbCrLf If PercSpace > 50 Then DiskDetail(intDisk) = DiskDetail(intDisk) & "<TR><TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""green"" TITLE=""" & CStr(PercSpace) & "%"">X</TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""" & CStr(100 - PercSpace) & "%"" BGCOLOR=""white""></TD>" & vbCrLf Else DiskDetail(intDisk) = DiskDetail(intDisk) & "<TR><TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""green"" TITLE=""" & CStr(PercSpace) & "%""></TD>" & vbCrLf DiskDetail(intDisk) = DiskDetail(intDisk) & "<TD WIDTH=""" & CStr(100 - PercSpace) & "%"" BGCOLOR=""white"">X</TD>" & vbCrLf End If DiskDetail(intDisk) = DiskDetail(intDisk) & "</TR></TABLE>" & vbCrLf Else DiskDetail(intDisk) = DiskDetail(intDisk) & "[Function disabled]" & vbCrLf End If DiskDetail(intDisk) = DiskDetail(intDisk) & "</TD></TR>" & vbCrLf intDisk = intDisk + 1 rsDiskSpace.MoveNext Wend OutFile.WriteLine("</TABLE>") OutFile.WriteLine("<A HREF=""#ReportIndex"">Back To Report Index</A>") OutFile.WriteLine(" <HR>") DiskDetail(intDisk) = "XXXXX" ' Disk Space and Datafile Reports OutFile.WriteLine("<A NAME=""diskfile"">") OutFile.WriteLine("Disk Space Summary Report ") intDisk = 1 While DiskDetail(intDisk) <> "XXXXX" OutFile.Write DiskDetail(intDisk) FileDetail = "" rsDBSize.MoveFirst While Not rsDBSize.EOF rsDBDetail.Open "EXEC master.dbo.sp_helpdb @dbname='" & rsDBSize.Fields.Item(0) & "'", adoConn DBName = rsDBDetail.Fields.Item(0) Set rsDBFiles = rsDBDetail.NextRecordset While Not rsDBFiles.EOF If UCase(Left(rsDBFiles.Fields.Item(2),1)) = UCase(DiskLetter(intDisk)) Then intFileSize = CDbl(Replace(rsDBFiles.Fields.Item(4)," KB","")) / 1024 If Right(rsDBFiles.Fields.Item(6),1) = "%" Then intGrowth = (CDbl(Replace(rsDBFiles.Fields.Item(4)," KB","")) / 100) * CDbl(Replace(rsDBFiles.Fields.Item(6),"%","")) Else intGrowth = CDbl(Replace(rsDBFiles.Fields.Item(6)," KB","")) End If intGrowth = intGrowth / 1024 FileDetail = FileDetail & "<TR>" & vbCrLf FileDetail = FileDetail & "<TD><A NAME=""#" & RTrim(rsDBFiles.Fields.Item(0)) & """>" & rsDBFiles.Fields.Item(0) & "</TD>" & vbCrLf FileDetail = FileDetail & "<TD><A HREF=""#" & DBName & """>" & DBName & "</A></TD>" & vbCrLf FileDetail = FileDetail & "<TD ALIGN=""RIGHT"">" & CStr(Round(intFileSize,0)) & " MB</TD>" & vbCrLf FileDetail = FileDetail & "<TD ALIGN=""RIGHT"">" & CStr(Round(intGrowth,2)) & " MB</TD>" & vbCrLf DiskSpace(intDisk) = DiskSpace(intDisk) - Round(intFileSize,0) FileDetail = FileDetail & "<TD>" & vbCrLf If DTSGlobalVariables("DISABLE_DISK_SIZE").Value = "NO" Then PercUsed =Round(intFileSize / PercOne(intDisk), 0) PercExt = Round(intGrowth / PercOne(intDisk),0) If (PercUsed + PercExt) > 100 Then PercExt = 100 - PercUsed PercSpace = 100 - (PercUsed + PercExt) FileDetail = FileDetail & "<TABLE WIDTH=""100%"" BORDER=0 CELLSPACING=0>" & vbCrLf If PercSpace < 50 Then FileDetail = FileDetail & "<TR><TD WIDTH=""" & CStr(PercUsed) & "%"" BGCOLOR=""red"" TITLE=""" & CStr(PercUsed) & "%"">X</TD>" & vbCrLf FileDetail = FileDetail & "<TD WIDTH=""" & CStr(PercExt) & "%"" BGCOLOR=""blue"" TITLE=""" & CStr(PercExt) & "%""></TD>" & vbCrLf FileDetail = FileDetail & "<TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""white""></TD>" & vbCrLf Else FileDetail = FileDetail & "<TR><TD WIDTH=""" & CStr(PercUsed) & "%"" BGCOLOR=""red"" TITLE=""" & CStr(PercUsed) & "%""></TD>" & vbCrLf FileDetail = FileDetail & "<TD WIDTH=""" & CStr(PercExt) & "%"" BGCOLOR=""blue"" TITLE=""" & CStr(PercExt) & "%""></TD>" & vbCrLf FileDetail = FileDetail & "<TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""white"">X</TD>" & vbCrLf End If FileDetail = FileDetail & "</TR></TABLE>" & vbCrLf Else FileDetail = FileDetail & "[Function disabled]" & vbCrLf End If FileDetail = FileDetail & "</TD></TR>" & vbCrLf End If rsDBFiles.MoveNext Wend If rsDBFiles.State = 1 Then rsDBFiles.Close If rsDBDetail.State = 1 Then rsDBDetail.Close rsDBSize.MoveNext Wend OutFile.WriteLine("<TR>") OutFile.WriteLine("<TD>[Non-DB File Size]</TD>") OutFile.WriteLine("<TD></TD>") OutFile.WriteLine("<TD ALIGN=""RIGHT"">" & CStr(DiskSpace(intDisk)) & " MB</TD>") OutFile.WriteLine("<TD></TD>") OutFile.WriteLine("<TD>") If DTSGlobalVariables("DISABLE_DISK_SIZE").Value = "NO" Then PercUsed =Round(DiskSpace(intDisk) / PercOne(intDisk), 0) PercSpace = 100 - PercUsed OutFile.WriteLine("<TABLE WIDTH=""100%"" BORDER=0 CELLSPACING=0>") If PercSpace < 20 Then OutFile.WriteLine("<TR><TD WIDTH=""" & CStr(PercUsed) & "%"" BGCOLOR=""red"" TITLE=""" & CStr(PercUsed) & "%"">X</TD>") OutFile.WriteLine("<TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""white""></TD>") Else OutFile.WriteLine("<TR><TD WIDTH=""" & CStr(PercUsed) & "%"" BGCOLOR=""red"" TITLE=""" & CStr(PercUsed) & "%""></TD>") OutFile.WriteLine("<TD WIDTH=""" & CStr(PercSpace) & "%"" BGCOLOR=""white"">X</TD>") End If OutFile.WriteLine("</TR></TABLE>") Else OutFile.WriteLine("[Function disabled]") End If OutFile.WriteLine("</TD></TR>") OutFile.Write FileDetail OutFile.WriteLine("</TABLE>") OutFile.WriteLine("<A HREF=""#ReportIndex"">Back To Report Index</A>") OutFile.WriteLine(" ") intDisk = intDisk + 1 Wend OutFile.WriteLine("<HR>") ' Write DB Detail Reports DBDetailRep = "" FielRepText = "" For intArr = 1 To 10 DBFileList(intArr,1) = "" DBFileList(intArr,2) = "" Next rsDBSize.MoveFirst While Not rsDBSize.EOF rsDBDetail.Open "EXEC master.dbo.sp_helpdb @dbname='" & rsDBSize.Fields.Item(0) & "'", adoConn intDBSize = CDbl(Replace(rsDBDetail.Fields.Item(1)," MB", "")) ' Write DB Detail Section DBDetailRep = DBDetailRep & "" & vbCrLf DBDetailRep = DBDetailRep & "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf DBDetailRep = DBDetailRep & "<TR><TD WIDTH=150 BGCOLOR=""NAVY"">Database Name</TD><TD><A NAME=""#" & rsDBSize.Fields.Item(0) & """>" & rsDBSize.Fields.Item(0) & "</TD></TR>" & vbCrLf DBDetailRep = DBDetailRep & "</TABLE>" & vbCrLf DBDetailRep = DBDetailRep & "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf DBDetailRep = DBDetailRep & "<TR><TD WIDTH=120 BGCOLOR=""NAVY"">DB Size</TD>" If intDBSize > CDbl(DTSGlobalVariables("FILE_SIZE_LIMIT").Value) Then DBDetailRep = DBDetailRep & "<TD WIDTH=22% BGCOLOR=""RED"">" & rsDBDetail.Fields.Item(1) & "</TD>" Else DBDetailRep = DBDetailRep & "<TD WIDTH=22%>" & rsDBDetail.Fields.Item(1) & "</TD>" End If DBDetailRep = DBDetailRep & "<TD WIDTH=120 BGCOLOR=""NAVY"">Owner</TD>" DBDetailRep = DBDetailRep & "<TD WIDTH=22%>" & rsDBDetail.Fields.Item(2) & "</TD>" DBDetailRep = DBDetailRep & "<TD WIDTH=120 BGCOLOR=""NAVY"">Created</TD>" DBDetailRep = DBDetailRep & "<TD WIDTH=22%>" & rsDBDetail.Fields.Item(4) & "</TD></TR>" & vbCrLf DBDetailRep = DBDetailRep & "</TABLE>" & vbCrLf DBDetailRep = DBDetailRep & "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf DBDetailRep = DBDetailRep & "<TR><TD WIDTH=120 BGCOLOR=""NAVY"">Status</TD>" DBDetailRep = DBDetailRep & "<TD>" & Replace(rsDBDetail.Fields.Item(5),","," ") & "</TD></TR>" & vbCrLf DBDetailRep = DBDetailRep & "</TABLE>" & vbCrLf Set rsDBDetail = rsDBDetail.NextRecordset DBDetailRep = DBDetailRep & "<TABLE BORDER=1 WIDTH=100%>" & vbCrLf DBDetailRep = DBDetailRep & "<TR BGCOLOR=""NAVY""><TD>File Name</TD>" DBDetailRep = DBDetailRep & "<TD>File Size</TD>" DBDetailRep = DBDetailRep & "<TD>File Path</TD>" DBDetailRep = DBDetailRep & "<TD>File Group</TD>" DBDetailRep = DBDetailRep & "<TD>Max Size</TD>" DBDetailRep = DBDetailRep & "<TD>Growth</TD>" DBDetailRep = DBDetailRep & "<TD>Usage</TD></TR>" & vbCrLf While Not rsDBDetail.EOF intFileSize = CDbl(Replace(rsDBDetail.Fields.Item(4)," KB","")) / 1024 DBDetailRep = DBDetailRep & "<TR><TD><A HREF=""#" & RTrim(rsDBDetail.Fields.Item(0)) & """>" & rsDBDetail.Fields.Item(0) & "</A></TD>" If intFileSize > CDbl(DTSGlobalVariables("FILE_SIZE_LIMIT").Value) Then DBDetailRep = DBDetailRep & "<TD ALIGN=""RIGHT"" BGCOLOR=""RED"">" & rsDBDetail.Fields.Item(4) & "</TD>" Else DBDetailRep = DBDetailRep & "<TD ALIGN=""RIGHT"">" & rsDBDetail.Fields.Item(4) & "</TD>" End If DBDetailRep = DBDetailRep & "<TD>" & rsDBDetail.Fields.Item(2) & "</TD>" DBDetailRep = DBDetailRep & "<TD>" & rsDBDetail.Fields.Item(3) & "</TD>" DBDetailRep = DBDetailRep & "<TD>" & rsDBDetail.Fields.Item(5) & "</TD>" DBDetailRep = DBDetailRep & "<TD>" & rsDBDetail.Fields.Item(6) & "</TD>" DBDetailRep = DBDetailRep & "<TD>" & rsDBDetail.Fields.Item(7) & "</TD></TR>" & vbCrLf ' Write File Detail Section rsDiskSpace.MoveFirst While (rsDiskSpace.Fields.Item(0) <> UCase(Left(rsDBDetail.Fields.Item(2),1))) and (Not rsDiskSpace.EOF) rsDiskSpace.MoveNext Wend rsDBDetail.MoveNext Wend DBDetailRep = DBDetailRep & "</TABLE>" & vbCrLf DBDetailRep = DBDetailRep & "<A HREF=""#ReportIndex"">Back To Report Index</A>" & vbCrLf DBDetailRep = DBDetailRep & " <HR>" & vbCrLf rsDBDetail.Close rsDBSize.MoveNext Wend OutFile.WriteLine("DB Detail Reports ") OutFile.Write(DBDetailRep) rsDBSize.Close rsDiskSpace.Close MailSubject = "DB Size Report for " & DTSGlobalVariables("TARGET_SERVER").Value ' Close report file OutFile.WriteLine("</BODY>") OutFile.WriteLine("</HTML>") OutFile.Close Set ReadFile = oFSO.OpenTextFile(DTSGlobalVariables("REPORT_PATH").Value, 1) 'Determine if report is to be emailed If DTSGlobalVariables("EMAIL_REPORT").Value = "YES" Then ' Set email properties AddrList = DTSGlobalVariables("ADDRESSES").Value Addr = "" For iChar = 1 To Len(AddrList) - 1 If Mid(AddrList, iChar, 1) = ";" Then olMail.Recipients.Add Addr Addr = "" Else Addr = Addr & Mid(AddrList, iChar, 1) End If Next olMail.Recipients.Add Addr olMail.Recipients.ResolveAll olMail.Subject = MailSubject olMail.HTMLBody = ReadFile.ReadAll olMail.Send End If ' Clean Up ReadFile.Close If DTSGlobalVariables("KEEP_REPORT_FILE").Value = "NO" Then oFSO.DeleteFile DTSGlobalVariables("REPORT_PATH").Value, True End If Set oFSO = Nothing Set olMail = Nothing Set olApp = Nothing Main = DTSTaskExecResult_Success adoConn.Close End Function

Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

by Joshua Feierman

SQLServerCentral.com

Maintenance and Management

When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.92 (12)

You rated this post out of 5. Change rating

2018-05-18 (first published: 2015-10-19)

4,855 reads

Discuss

SQL Server Index Maintenance Performance Tuning for Large Tables

by Additional Articles

SQL Server Performance

Maintenance and Management

If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.

2011-08-18

5,492 reads

Top Tips for Effective Database Maintenance

by Additional Articles

TechNet Blogs

Maintenance and Management

Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.

2008-08-13

6,696 reads

Script to Change Stored Procedure Owner to DBO

by PBirch

SQLServerCentral

Maintenance and Management

This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

2001-10-28

1,599 reads

Discuss

Query to Display Trigger Status

by Additional Articles

SQLServerCentral

Maintenance and Management

Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

2001-10-28

1,273 reads

Discuss

SQL Server DB Size Report

Rate

Share

Share

Rate

SQL Server DB Size Report

Rate

Share

Share

Rate

Related content

Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

SQL Server Index Maintenance Performance Tuning for Large Tables

Top Tips for Effective Database Maintenance

Script to Change Stored Procedure Owner to DBO

Query to Display Trigger Status