Click here to Skip to main content
Click here to Skip to main content
Search within:



 
Comments & Discussions (2)

SQL Server: Script to Generate HTML Report/mail for Databses Current Size, Growth Rate and Available Disk Space

By , 17 Sep 2012
 
Working with multiple databases on multiple instances is a tough job. You need to monitor all these instances for everything. Best way to keep eye on every instance activity is SQL Server Jobs.

How quickly databases on these instances are growing and does target instance has required space on hard drives ? These are basic questions which every DBA keeps in mind during instance monitoring.

Amna Asif has suggested a better script to create a proper report (to mail) for actual database space, required and currently available on hard drives.


Download Script 

/*
 Script to send an alert through mail, with information that how many drive
 space is required from next databases growth on a specific instance and how many
 space is available.


 Script By: Amna Asif for ConnectSQL.blogspot.com
 */


 DECLARE@dbName varchar(200),
    @Qry Nvarchar(max)
 DECLARE@dbsize VARCHAR(50),
    @logsize VARCHAR(50),
    @reservedpages VARCHAR(50),
    @usedpages VARCHAR(50),
    @pages VARCHAR(50)


 SET @dbName = ''


---Get LOG File Spaces of All Databases--
 CREATE TABLE #LogSpaceStats
    (
      RowID INTIDENTITY
                PRIMARYKEY,
      dbName SYSNAME,
      Totallogspace DEC(20, 2),
      UsedLogSpace DEC(20, 2),
      Status CHAR(1)
    )
   
 INSERT#LogSpaceStats
        ( dbName, Totallogspace,UsedLogSpace, Status)
        EXEC ( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS'
            )
    
--Get Info of All Drives
 DECLARE@ServerDrives TABLE
    (
      RowID intIDENTITY
                PRIMARYKEY,
      Drive char,
      DriveSpace varchar(100),
      Required_Space varchar(100)
    )
 INSERT INTO @ServerDrives
        ( Drive, DriveSpace )
        EXEC master.sys.xp_fixeddrives
--Temporary Table to hold requried data
 CREATE TABLE#ServerFileStats
    (
      RowID INTIDENTITY
                PRIMARYKEY,
      dbName SYSNAME,
      Database_DSize varchar(100),
      Allocated_Space varchar(100),
      Unallocated_Space varchar(100),
      Unused varchar(100),
      Database_LSize varchar(100),
      UsedLogSpace DEC(20, 2),
      FreeLogSpace DEC(20, 2),
      FDataFileGrowth DEC(20, 2),
      FLogFileGrowth DEC(20, 2),
      DataFileDrive char,
      LogFileDrive char
    )
  
--Cursor Used to get each database size on given instance
 DECLAREcur_dbName CURSOR
    FOR SELECT  NAME
        FROM    SYS.DATABASES
        WHERE   state_desc ='ONLINE'
                ANDis_read_only = 0
 OPENcur_dbName
 FETCH NEXT FROM cur_dbName into @dbName
 WHILE @@FETCH_Status = 0
    BEGIN
        SELECT  @Qry = ' SELECT @dbsizeOUT = sum(convert(bigint,
                              case when status & 64 = 0 then size
                              else 0 end))
                              ,@logsizeOUT = sum(convert(bigint,
                                    case when status & 64 <> 0 then size
                                    else 0 end)) 
                                       FROM [' +@dbName + '].dbo.sysfiles '
                             
        EXEC sp_executesql @Qry,
            N'@dbsizeOUT  nvarchar(50) OUTPUT,@logsizeOUT  nvarchar(50) OUTPUT',
            @dbsizeOUT =@dbsize OUTPUT,@logsizeOUT = @logsize OUTPUT; 


        SELECT  @Qry = ' SELECT @reservedpagesOUT = sum(a.total_pages)
                                 ,@usedpagesOUT = sum(a.used_pages)
                      FROM [' +@dbName + '].sys.partitions p join [' + @dbName
                +'].sys.allocation_units a on p.partition_id = a.container_id 
                      LEFT JOIN [' +@dbName
                +'].sys.internal_tables it on p.object_id = it.object_id'


        EXEC sp_executesql @Qry,
     N'@reservedpagesOUT  nvarchar(50) OUTPUT,@usedpagesOUT nvarchar(50) OUTPUT',
            @reservedpagesOUT = @reservedpages OUTPUT,
            @usedpagesOUT = @usedpages OUTPUT ; 
       
        SELECT  @Qry = ' INSERT INTO #ServerFileStats                
                         SELECT DB_size.Database_Name
                         , DB_size.Database_DSize
                         , DB_size.Allocated_Space
                         , DB_size.Unallocated_Space
                         , DB_size.Unused
                         , DB_size.Database_LSize
             , (lss.TotalLogSpace*(lss.UsedLogSpace/100)) UsedLogSpace
             , (TotalLogSpace-(TotalLogSpace*(UsedLogSpace/100))) FreeLogSpace
             ,CASE mfD.is_percent_growth
              WHEN 0 THEN CONVERT(DEC(15,2),(mfD.growth* 8192 / 1048576))
              ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,'' MB'',''''))
                              *mfD.growth/100)) END  FDataFileGrowth
                          ,
                          CASE mfL.is_percent_growth WHEN 0 THEN CONVERT(DEC(15,2),(mfL.growth* 8192 / 1048576))
                          ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,'' MB'',''''))
                          *mfL.growth/100)) END  FLogFileGrowth
                         ,LEFT(mfD.physical_name,1) DataFileDrive
                         ,LEFT(mfL.physical_name,1) LogFileDrive
                         FROM
                         (
                          SELECT Database_Name = ''' + @dbName
                +'''
, Database_DSize = ltrim(str((convert (dec (15,2),'
       +@dbsize
       + '))* 8192 / 1048576,15,2) + '' MB'')
, ''Allocated_Space''=ltrim(str((CASE WHEN '
       +@dbsize + ' >= '+ @reservedpages
       + '
THEN convert (DEC (15,2),'
                +@reservedpages
                +')* 8192 / 1048576
ELSE 0 end),15,2) + '' MB'') 
                                    , ''Unallocated_Space'' = ltrim(str((CASE WHEN '
               +@dbsize + ' >= '+ @reservedpages
                +'
THEN  (convert (DEC (15,2),'
                +@dbsize + ') - convert (DEC (15,2),' + @reservedpages
                +'))* 8192 / 1048576
ELSE 0 end),15,2) + '' MB'')
                                    , ''Unused'' =ltrim(str((CAST(('
                +@reservedpages + ' - '+ @usedpages
                +')AS BIGINT) * 8192 / 1024.)/1024,15,2) + '' MB'') 
                , Database_LSize = ltrim(str((convert (dec (15,2),'
                +@logsize
                + '))* 8192 / 1048576,15,2) + '' MB'')
  )DB_size LEFT JOIN #LogSpaceStats AS lss on lss.dbName=DB_size.Database_Name
                          INNER JOIN ' +@dbName
                +'.sys.databases db ON DB.name=DB_size.Database_Name
                          INNER JOIN ' +@dbName
                +'.sys.master_files mfD on mfD.database_id=DB.database_id AND mfD.type_desc=''ROWS''
                          INNER JOIN ' +@dbName
                +'.sys.master_files mfL on mfL.database_id=DB.database_id AND mfL.type_desc=''LOG'''


        EXEC ( @Qry


            )
  FETCH NEXT FROM cur_dbName into @dbName
    END
 CLOSEcur_dbName
 DEALLOCATEcur_dbName


 UPDATE@ServerDrives
 SET    Required_Space =SumDriveS.sumofdrivespcae
 FROM   ( SELECT    SUM(CONVERT(DEC(20, 2), sumofdrivespcae)) sumofdrivespcae,
                    DRIVE AS DRIVE
          FROM      ( SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FDataFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.DataFileDrive ASDRIVE
                      FROM      #ServerFileStats fss
                      GROUPBY  fss.DataFileDrive
                      UNION
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.LogFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUPBY  fss.LogFileDrive )SumDrive
          GROUPBY  SumDrive.DRIVE )SumDriveS
        LEFT OUTER JOIN@ServerDrives sd on SumDriveS.Drive = sd.Drive


------------------------------------------------------------------------------
-----------------------------------------Report Mailing-----------------------
DECLARE @Loop int
 DECLARE@Subject varchar(100)
 DECLARE@strMsg varchar(4000)


 SELECT@Subject = 'SQL Monitor Alert: ' + @@SERVERNAME+ '        '
        + Convert(varchar, GETDATE())
  Declare @Body varchar(max),
    @TableHead varchar(1000),
    @TableTail varchar(1000),
    @TableHead2 varchar(1000),
    @Body2 varchar(3000)
 Set NoCount On ;
-- Create HTML mail body
 Set @TableTail = '</table></body></html>';
  Set @TableHead = '<html><head>'+ '<style>'
    + 'td {border: solid black 1px;padding-left:3px;padding-right:3px;padding-top:2px;padding-bottom:2px;font-size:10pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Database Name</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>File Group</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Total Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Allocated Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Unallocated Space</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF Unused</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Total Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Used Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Unused Space</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF FileGrowth</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>LF FileGrowth</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF Drive</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b> LF Drive </b></td></tr>' ;


  Select @Body = ( SELECT    td = CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY dbName ))
                            + CHAR(10),
                            td = ISNULL(dbName, 'Unknown') + CHAR(10),
                            td = ISNULL('Data/LOG', 'Unknown') + CHAR(10),
                            td = ISNULL(Database_DSize, '0.00') + CHAR(10),
                            td = ISNULL(Allocated_Space, '0.00') + CHAR(10),
                            td = ISNULL(Unallocated_Space, '0.00') + CHAR(10),
                            td = ISNULL(Unused, '0.00') + CHAR(10), '',
                            td = ISNULL(Database_LSize, '0.00') + CHAR(10),
                            td = ISNULL(convert(varchar, UsedLogSpace), '0.00')
                            +' MB' + CHAR(10),
                            td = ISNULL(convert(varchar, FreeLogSpace), '0.00')
                            + ' MB' + CHAR(10),
                            td = ISNULL(convert(varchar, FDataFileGrowth),
                                        '0.00') + ' MB' + CHAR(10), '',
                            td = ISNULL(convert(varchar, FLogFileGrowth),
                                        '0.00') + ' MB' + CHAR(10), '',
                            td = ISNULL(DataFileDrive, '0') + CHAR(10), '',
                            td = ISNULL(LogFileDrive, '0') + CHAR(10), ''
                  FROM      #ServerFileStats
                  ORDERBY  dbName
        FOR       XML RAW('tr'),
                      ELEMENTS)


-- Replace the entity codes and row numbers
 Set @Body = Replace(@Body, '_x0020_', space(1))
 Set @Body = Replace(@Body, '_x003D_', '=')
 Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
 Set @Body = Replace(@Body, '<TRRow>0</TRRow>','')


 DECLARE @flag BIT
 SELECT @flag = 1
 FROM   @ServerDrives
 WHERE  convert(dec(15, 2), DriveSpace) < convert(dec(15, 2), Required_Space)
        * 2
 SET @flag = ISNULL(@flag, 0)


 SET@TableHead2 = '<html><head>'+ '<style>'
    + 'td {border: solid black 1px;padding-left:1px;padding-right:1px;padding-top:1px;padding-bottom:1px;font-size:8pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Drive</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Drive Space</b></td> '


 IF ( @flag = 0 )
    set@TableHead2 = @TableHead2
     + '<td align=center bgcolor=#E6E6FA><b>Required Drive Space</b></td></tr>' ;
 ELSE
    set@TableHead2 = @TableHead2
     + '<td align=center bgcolor=#FF7F50><b>Required Drive Space</b></td></tr>' ;
         
 Select @Body2 = ( SELECT   td = ROW_NUMBER() OVER ( ORDER BY Drive ),
                            td = ISNULL(Drive, 'Unknown') + char(10),
                            td = ISNULL(DriveSpace + ' MB', 0) + char(10),
                            td = ISNULL(Required_Space + ' MB', 0)
                   FROM     @ServerDrives sd
        For        XML RAW('tr'),
                       Elements)


 Select @Body = @TableHead2 +@Body2 + @TableTail +'<br/><br/><br/><br/>'
        +@TableHead + @Body +@TableTail
-- Send mail
 EXEC msdb.dbo.sp_send_dbmail
      @recipients = 'abc@xyz.com',
    @subject =@Subject,
    @profile_name ='MyMailProfileName',
    @body =@Body,
    @body_format ='HTML' ;


 --Drop Temporary Tables When Not Required
 DROP TABLE #ServerFileStats
 DROP TABLE#LogSpaceStats


DF = Data Files
LF = LogFiles

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

 aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Member
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

Article Top
 
Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board.
Search this forum  
    Spacing  Noise  Layout  Per page   
First Prev Next
SuggestionFormatting...mvpSandeep Mewara14 Sep '12 - 23:42 
Please format your code part using PRE tags.
Sandeep Mewara
Microsoft ASP.NET MVP
 
[My latest Article]: Server side Delimiters in ASP.NET[^]
Sign In·View Thread·Permalink
AnswerFormattingmemberClifford Nelson14 Sep '12 - 17:46 
I would fix formatting of the code. it is very small and has problems with contrast.
Sign In·View Thread·Permalink
Last Visit: 31 Dec '99 - 18:00     Last Update: 24 May '13 - 19:43Refresh1

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

About Article
Working with multiple databases on multiple instances is a tough job. You need to monitor all these instances for everything. Best way to keep eye on every instance activity is SQL Server Jobs. How quickly databases on these instances are growing and does target instance has required space on hard d
Type Technical Blog
Licence CPOL
First Posted 15 Sep 2012
Views 5,763
Bookmarked 11 times
Top News

Old Japanese Man Creates Amazing Art Using Excel (Wait, Excel?)

Get the Insider News free each morning.
Related Videos
JavaScript: HTML Script Element
HTML 5 - 0504 Working With Font Sizes
SQL Server 2005 Distilled - Chapter 3: Enterprise Data Management
Database Snapshot in MS SQL Server
Monitoring Free Space on Microsoft SQL Server
99.99% available ASP.NET and SQL Server SaaS Production Architecture
MailMergeLib - A .NET Mail Client Library
Speeding up database access - Part 8: Fixing memory, disk, and CPU issues
bttlxeForum - Advanced ASP Forum Software
Upgrading to Lotus Notes and Domino 7 - Chapter 3 : Domino Domain Monitoring
Microsoft Reporting Services in Action
Dynamic Management Views [DMV] – A SQL Server 2005 Feature
RSS Feed Aggregator and Blogging Smart Client
Generating Word Reports / Documents
A rating system to rate your online articles and display the ratings graphically
Migrating data from on-premise SQL Server to SQL Azure
XHTML2RTF: An HTML to RTF conversion tool based on XSL
Fast ASP.NET Web page loading by downloading multiple JavaScripts after visible content and in batch
Internet programming primer (general overview and history of ASP.NET)
Dell SharePoint Infrastructure Optimization with AvePoint
PHP Security
Document SQL Server 2000/2005 Database
Permalink | Advertise | Privacy | Mobile
Web02 | 2.6.130523.1 | Last Updated 17 Sep 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid