Database Growth status report for All DB(s) via Email in SQL Server

 DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  


Declare  @dbsize  Table

(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 


insert into @dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 

exec sp_msforeachdb 

'use [?]; 

  select DB_NAME() AS DbName, 

  CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  

  CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  

  sum(size)/128.0 AS File_Size_MB, 

  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 

  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  

  from sys.database_files  where type=0 group by type' 

  -------------------log size-------------------------------------- 


--  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 


--drop table #logsize 


Declare  @logsize  Table

 (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 

 

insert into @logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 

exec sp_msforeachdb 

'use [?]; 

  select DB_NAME() AS DbName, 

  sum(size)/128.0 AS Log_File_Size_MB, 

  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 

  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  

  from sys.database_files  where type=1 group by type' 

  --------------------------------database free size 


  --if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 

  --drop table #dbfreesize 


Declare  @dbfreesize Table

(name sysname,database_size varchar(50),Freespace varchar(50)default (0.00))   

insert into @dbfreesize(name,database_size,Freespace) 


exec sp_msforeachdb 

'use [?];SELECT database_name = db_name() 

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 

    ,''unallocated space'' = ltrim(str(( 

                CASE  

                    WHEN dbsize >= reservedpages 

                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 

                    ELSE 0 

                    END 

), 15, 2) + '' MB'') 

FROM ( 

    SELECT dbsize = sum(convert(BIGINT, CASE  

                    WHEN type = 0 

                        THEN size 

                    ELSE 0 

                    END)) 

        ,logsize = sum(convert(BIGINT, CASE  

                    WHEN type <> 0 

                        THEN size 

                    ELSE 0 

                    END)) 

    FROM sys.database_files 

) AS files 

,( 

    SELECT reservedpages = sum(a.total_pages) 

        ,usedpages = sum(a.used_pages) 

        ,pages = sum(CASE  

                WHEN it.internal_type IN (202,204,211,212,213,214,215,216 

      ) 

THEN 0 


                WHEN a.type <> 1 


                    THEN a.used_pages 


                WHEN p.index_id < 2 


                    THEN a.data_pages 


                ELSE 0 


                END) 


    FROM sys.partitions p 


    INNER JOIN sys.allocation_units a 


        ON p.partition_id = a.container_id 


    LEFT JOIN sys.internal_tables it 


        ON p.object_id = it.object_id 


) AS partitions' 


----------------------------------- 

--if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 

--drop table #alldbstate  


Declare @alldbstate  Table

(dbname sysname,DBstatus varchar(55),R_model Varchar(30)) 

--select * from sys.master_files 

insert into @alldbstate (dbname,DBstatus,R_model) 

select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 

--select * from #dbsize 


insert into @dbsize(Dbname,dbstatus,Recovery_Model) 

select dbname,dbstatus,R_model from @alldbstate where DBstatus <> 'online' 

insert into @logsize(Dbname) 

select dbname from @alldbstate where DBstatus <> 'online' 

insert into @dbfreesize(name) 

select dbname from @alldbstate where DBstatus <> 'online' 


Declare @DB_space_Details Table

    (

[Dbname] [sysname] NOT NULL,

[dbstatus] [varchar](50) NULL,

[Recovery_Model] [varchar](40) NULL,

[DBsize] [decimal](38, 2) NULL,

[file_Size_MB] [decimal](30, 2) NULL,

[Space_Used_MB] [decimal](30, 2) NULL,

[Free_Space_MB] [decimal](30, 2) NULL,

[Log_File_Size_MB] [decimal](38, 2) NULL,

[log_Space_Used_MB] [decimal](30, 2) NULL,

[log_Free_Space_MB] [decimal](30, 2) NULL,

[DB_Freespace] [varchar](50) NULL,

[Capture_Date] [datetime] NOT NULL

Insert into @DB_space_Details 

select  

d.Dbname,d.dbstatus,d.Recovery_Model, 

(file_size_mb + log_file_size_mb) as DBsize, 

d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 

l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace,getdate() 'Current_Date'  

from @dbsize d join @logsize l  

on d.Dbname=l.Dbname join @dbfreesize fs  

on d.Dbname=fs.name 

order by Dbname


--Select * from @DB_space_Details 

--Select * from DB_space_Details


SET @xml = CAST(( SELECT DB.DBname As 'td',' ',DB.dbstatus AS 'td' ,' ',DB.Recovery_Model as 'td' ,' ',DB.DBsize 'td',' ',DB.File_Size_MB as 'td',  

' ',DB.Space_Used_MB As 'td' ,' ',DB.Free_Space_MB as 'td',' ',  DB.Log_File_Size_MB As 'td', ' ', DB.Log_Space_Used_MB As 'td', ' ',

 DB.log_free_Space_MB As 'td',' ', DB.DB_Freespace As 'td' ,' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @DB_space_Details  DB

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  


SET @body ='<html><body><H5>Database Growth status </H5>  

<table border = 1>   

<th> Database Name </th><th> Ddbstatus </th> <th> Recovery_Model </th> <th> DBsize </th> <th> File_Size_MB </th> <th> Space_Used_MB </th>  

<th> Free_Space_MB </th><th> Log_File_Size_MB </th> 

<th> Log_Space_Used_MB </th> <th> log_free_Space_MB </th> <td bgcolor=#F0E68C><b>DB_Freespace<b> </td> <th>Capture_Date</th></tr>'      


SET @body = @body + @xml +'</table></body></html>'  

Set @cmd= @@SERVERNAME + ': Database Space '  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  


EXEC msdb.dbo.sp_send_dbmail  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

@body = @body,  

@body_format ='HTML',  

@recipients =  'ajeyudub@sqldbanow.com', -- replace with your email address  

@subject = @cmd

No comments:

Post a Comment