Monday, July 16, 2012

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

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.
 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 INT IDENTITY
                PRIMARY KEY,
      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 int IDENTITY
                PRIMARY KEY,
      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 INT IDENTITY
                PRIMARY KEY,
      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
 DECLARE cur_dbName CURSOR
    FOR SELECT  NAME
        FROM    SYS.DATABASES
        WHERE   state_desc = 'ONLINE'
                AND is_read_only = 0
 OPEN cur_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
 CLOSE cur_dbName
 DEALLOCATE cur_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 AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.DataFileDrive
                      UNION
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.LogFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.LogFileDrive ) SumDrive
          GROUP BY  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
                  ORDER BY  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

16 comments:

  1. i ve used above script but i'm getting following error

    Msg 102, Level 15, State 1, Line 7
    Incorrect syntax near '.'.
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '.'.
    Msg 102, Level 15, State 1, Line 33
    Incorrect syntax near '.'.

    ReplyDelete
  2. Possibly you have database name with space or special characters. Script updated.

    ReplyDelete
  3. very nice good work !!!!!!

    ReplyDelete
  4. I'm trying to put a link inside the Td but it does not work. Any suggestion? Thanks.
    D.V

    ReplyDelete
  5. Amazing Script..... Great Work.
    Thank you.

    ReplyDelete
  6. Aasim,
    can we schedule a job like this to get us the report for all production server in one email itself?

    ReplyDelete
  7. Excellent script for monitoring database size, I want to know as output required drive space 218.67 MB?
    As of now E drive 1.01 TB Free space available of Total 1.09 TB

    Pl. explain

    ReplyDelete
    Replies
    1. ananda, Thanks your appreciations. Can you please share complete result (html) (please share it through mail), so we can figure out what's missing.

      Delete
    2. Row ID Drive Drive Space Required Drive Space
      1 C 78510 MB 0
      2 D 93687 MB 0
      3 E 2122608 MB 144.97 MB

      what does it means? required drive space 144.97 MB, does it next data growth size?

      Delete
  8. I get this error for some of the larger databases. The error is in the INSERT INTO #ServerFileStats subquery.
    Arithmetic overflow error converting expression to data type int.

    Any idea ?

    ReplyDelete
  9. Hello,
    Using MSSQL 2005, we have problems with your script.
    These were causing errors, in order : SYSNAME (replaced by sysname), SYS.DATABASES (replaced by sys.databases), and Drive (Invalid column name 'Drive'.) for this one we can't find the root cause.
    Can you please help us ?
    thanks in advance.

    ReplyDelete
  10. Hi Assim, This is very awasome script. Thanks for sharing it.

    ReplyDelete
  11. What does required drive space mean ?
    Row ID Drive Drive Space Required Drive Space
    1 C 11216 MB 0
    2 D 33558 MB 0
    3 E 182200 MB 0
    4 F 70087 MB 402319.82 MB
    5 G 16971 MB 1002.00 MB
    6 H 18851 MB 43694.20 MB
    7 I 10247 MB 552134.96 MB
    8 J 19940 MB 1107857.19 MB
    9 L 51964 MB 1084008.89 MB
    10 M 629 MB 0
    11 P 34093 MB 529663.89 MB
    12 Q 621 MB 0
    13 T 38098 MB 919864.89 MB

    ReplyDelete
  12. Nice Job Done Aasin.Really appreciate your work.

    ReplyDelete

All suggestions are welcome