SQL SERVER DBA, Linux and Azure

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

DB_Growth status Report Alert

Hi Team,

Please find below mentioned script to get an alert DB_Growth status Report via email.

Once executed the following script and then run exec [DB_Growth] proc to get an alert


 USE [master]

GO


/****** Object:  StoredProcedure [dbo].[DB_Growth]    Script Date: 10/28/2021 5:26:50 AM ******/

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE Proc [dbo].[DB_Growth]  

  

As  

  

Begin  

  

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

  

DECLARE @xml NVARCHAR(MAX)  

  

DECLARE @body NVARCHAR(MAX)  

  

  

  

DECLARE @DBSpaceInfo TABLE     

  

( ServerName VARCHAR(100),     

  

  DatabaseName VARCHAR(100),   

  

  PhysicalFileName NVARCHAR(520),     

  

  FileSizeMB DECIMAL(10,2),     

  

  SpaceUsedMB DECIMAL(10,2),    

  

  FreeSpaceMB DECIMAL(10,2),   

  

  FreeSpacePct varchar(8),  

  

  Capture_Date Datetime   

  

)   

  

  

  

 SELECT @command = 'Use [' + '?' + '] SELECT     

  

 @@servername as ServerName,     

  

 ' + '''' + '?' + '''' + ' AS DatabaseName   , name   

  

, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB   

  

, convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB   

  

, convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB,   

  

CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,  

  

Getdate() As Capture_Date  

  

from sys.database_files a'   

  

  

  

INSERT INTO @DBSpaceInfo   

  

EXEC sp_MSForEachDB @command     

  

  

  

  

  

SET @xml = CAST(( SELECT DB.ServerName As 'td',' ',DB.DatabaseName AS 'td' ,' ',DB.PhysicalFileName as 'td' ,' ',DB.FileSizeMB as 'td',' ',Db.SpaceUsedMB as 'td',  

  

' ',DB.FreeSpaceMB as 'td' ,' ',DB.FreeSpacePct as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @DBSpaceInfo  DB  

  

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

  

  

  

  

  

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

  

<table border = 1>   

  

<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th>  

  

<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'      

  

--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'      

  

   

  

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

  

  

  

Set @cmd= @@SERVERNAME + ': Database Growth status '  

  

  

  

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 = 'sqldbanow@gmail.com', -- replace with your email address  

  

@subject = @cmd  

  

End  

 

GO






Configure Deadlock Alert Using T-SQL in SQL Server

-- Here is a script to configure Deadlock Alert using t-sql


USE msdb

GO

 

DECLARE @perfcond NVARCHAR (100);

DECLARE @sqlversion TINYINT ;

 

-- get the major version of sql running

SELECT  @sqlversion = ca2.Ver

FROM    (SELECT CONVERT(VARCHAR (20),

                    SERVERPROPERTY('ProductVersion' )) AS Ver) dt1

        CROSS APPLY (SELECT CHARINDEX( '.', dt1 .Ver) AS Pos ) ca1

        CROSS APPLY (SELECT SUBSTRING( dt1.Ver , 1, ca1.Pos -1) AS Ver ) ca2;

 

-- handle the performance condition depending on the version of sql running

-- and whether this is a named instance or a default instance.

 

SELECT  @perfcond =

        CASE WHEN @sqlversion >= 11 THEN ''

        ELSE ISNULL (N'MSSQL$' +

                CONVERT(sysname , SERVERPROPERTY ('InstanceName')), N'SQLServer') + N':'

        END +

        N'Locks|Number of Deadlocks/sec|_Total|>|0' ;

 

EXEC msdb. dbo.sp_add_alert

    @name= N'Deadlock Alert',

    @message_id =0,

    @severity =0,

    @enabled =1,

    @delay_between_responses =0,

    @include_event_description_in =0,

    @category_name =N'[Uncategorized]',

    @performance_condition =@perfcond,

    --@job_name=N'Job to run when a deadlock happens, if applicable'

    -- or

    @job_id =N'00000000-0000-0000-0000-000000000000'

GO

 

EXEC msdb. dbo.sp_add_notification

    @alert_name = N'Deadlock Alert',

    @notification_method = 1, --email

    @operator_name = N'<operatorName>' ; -- name of the operator to notify

GO