Send Email for Service Restarts in SQL server

 --Send Email for Service Restarts in SQL server


USE msdb

GO

-- Declare variables for necessary email content

DECLARE @ServerName VARCHAR(128),

@ComputerNamePhysicalNetBIOS VARCHAR(128),

@Datetime DATETIME,

@EmailRecipients VARCHAR(512),

@EmailSubject VARCHAR(128),

@MessageBody VARCHAR(512)


-- Set variables to proper values

SELECT @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),

@ServerName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128)),

@Datetime = GETDATE(),

@EmailRecipients = 'bajeyudu@sqldbanow.com', -- if more than one email address use ; between email addresses

@EmailSubject = 'SQL Server Services Have Been Started!!!'


SELECT @MessageBody = 'SQL Server services have been started on a SQL Server Instance named ' + @ServerName + CHAR(13) +

'running on windows server ' + @ComputerNamePhysicalNetBIOS + '.' + CHAR(13) + CHAR(13) +

'Investigate the service restart if it has not been communicated.'


EXEC sp_send_dbmail

@recipients = @EmailRecipients,

@subject = @EmailSubject,

@body = @MessageBody,

@body_format = 'TEXT'


Suspect Pages Alert in SQL Server

 --By using below mentioned script will get suspect pages report via email 

declare @count integer

declare @tableHTML  nvarchar(MAX);

declare @subj nvarchar(100)


select @count=count(1)

from msdb.dbo.suspect_pages;


set @subj = 'Suspect Pages Found in ' + @@SERVERNAME;


set @tableHTML =

    N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +

    N'<table border="1">' +

    N'<tr><th>Database ID</th><th>Database</th>' +

    N'<th>File ID</th><th>File</th><th>Page ID</th>' +

    N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +

    cast ( ( select td = sp.database_id,       '',

       td = d.name,       '',

       td = sp.file_id,       '',

       td = mf.physical_name,       '',

       td = sp.page_id,       '',

       td = case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page'

            when sp.event_type = 2 then 'Bad checksum'

            when sp.event_type = 3 then 'Torn Page'

            when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)'

            when sp.event_type = 5 then 'Repaired (DBCC repaired the page)'

            when sp.event_type = 7 then 'Deallocated by DBCC'

       end,       '',

       td = sp.error_count,       '',

       td = sp.last_update_date

from msdb.dbo.suspect_pages sp

inner join sys.databases d on d.database_id=sp.database_id

inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id

              for xml path('tr'), TYPE 

    ) as nvarchar(max) ) +

    N'</table>' ;


IF @count > 0

  exec msdb.dbo.sp_send_dbmail

    @recipients=N'bandaruajeyudu@outlook.com',

    @body= @tableHTML, 

    @subject = @subj,

    @body_format = 'HTML',

    @profile_name ='SQLMail'



GO


delete from msdb.dbo.suspect_pages

where last_update_date < getdate()-90;

SQL Jobs Failed report via email from SQL Server

Using below mentioned script to get failed jobs report in SQL Server.


create table Failed_Jobs (

[Status] [varchar](6) NOT NULL,

[Job Name] [varchar](100) NULL,

[Step ID] [varchar](5) NULL,

[Step Name] [varchar](30) NULL,

[Start Date Time] [varchar](30) NULL,

[Message] [nvarchar](4000) NULL)


insert into Failed_Jobs

select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name",

       cast(sjs.step_id as varchar(5)) as "Step ID",

       cast(sjs.step_name as varchar(30)) as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobsteps sjs 

 on sj.job_id = sjs.job_id

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id

where sjh.run_status <> 1

  and cast(sjh.run_date as float)*1000000+sjh.run_time > 

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am

union

select 'FAILED',cast(sj.name as varchar(100)) as "Job Name",

       'MAIN' as "Step ID",

       'MAIN' as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id

where sjh.run_status <> 1 and sjh.step_id=0

  and cast(sjh.run_date as float)*1000000+sjh.run_time >

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at


declare @cnt int  

select @cnt=COUNT(1) from Failed_Jobs    

if (@cnt > 0)

begin


declare @strsubject varchar(100)

select @strsubject='Check the following failed jobs on ' + @@SERVERNAME


declare @tableHTML  nvarchar(max);

set @tableHTML =

N'<H1>Failed Jobs Listing - ' + @@SERVERNAME +'</H1>' +

N'<table border="1">' +

N'<tr><th>Status</th><th>Job Name</th>' +

N'<th>Step ID</th><th>Step Name</th><th>Start Date</th>' +

N'<th>Message</th></tr>' +

CAST ( ( SELECT td = [Status], '',

                    td = [Job Name], '',

                    td = [Step ID], '',

                    td = [Step Name], '',

                    td = [Start Date Time], '',

                    td = [Message]

  FROM Failed_Jobs

  FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail

@from_address='BANDARUAJEYUDU@OUTLOOK.com',

@recipients='BANDARUAJEYUDU@OUTLOOK.com',

@subject = @strsubject,

@body = @tableHTML,

@body_format = 'HTML' ,

@profile_name='SQLMail'

end


drop table Failed_Jobs

Automatic alert when cluster fail over happened

When  fail over happen, both SQL server and Agent service will get restarted, Then we will get Notification Alert When Fail Over Happened 


DECLARE @importance AS VARCHAR(6)

DECLARE @body AS NVARCHAR(1000)

DECLARE @Subject  AS NVARCHAR(1000)

DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)

DECLARE @recipients VARCHAR(100)

SELECT  @InstanceName =@@SERVERNAME

SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))

SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName

SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName

SELECT  @importance ='High'

EXEC msdb.dbo.sp_send_dbmail

     @profile_name ='sqlmail',

         @recipients ='bajeyudu@SQLDBANOW.com;',

     --@recipients=@recipientsList,

     @subject = @subject ,

     @body = @body,

     @body_format = 'HTML' ,

     @importance=@importance

Trigger for enable and disable jobs alerts in SQL Server

below mentioned trigger script to monitor the jobs enable & disable alerts. 

USE [msdb]

GO

 

CREATE TRIGGER [dbo].[JobStatusAlert]

   ON [dbo].[sysjobs]

   AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

 

-- Check if job is enabled/disabled

DECLARE @MailBody VARCHAR(300)

-- Check if job status is changed (enabled/disabled)

IF (SELECT TOP 1 CASE WHEN d.enabled = i.enabled THEN 0 ELSE 1 END

FROM deleted d INNER JOIN inserted i

ON d.job_id = I.job_id) = 1

BEGIN

 

-- Get session detail and create a message

SELECT TOP 1 @MailBody = 'Job "'

+i.name

+'" is recently '

+CASE WHEN i.enabled = 0

THEN ' DISABLED '

ELSE ' ENABLED 'END

+ ' by user '

+login_name

+ ' with session id '

+ CAST (c.session_id AS VARCHAR(3))

+' and host name '

+host_name +' at '

+ CONVERT(VARCHAR(50),last_request_end_time,109)

FROM sys.dm_exec_connections c

INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)

CROSS APPLY inserted i

WHERE text LIKE '%exec msdb.dbo.sp_help_job%'

AND text NOT LIKE '%SELECT c.session_id'

ORDER BY last_read DESC

 

-- Send mail to DBA Team

EXEC msdb.dbo.sp_send_dbmail

@recipients='bajeyudu@SQLDBANOW.com', -- change mail address accordingly

@subject = 'Job Status Changed at SQLDBANOWDB01 Server',

@profile_name = 'Sqlmail', -- Change profile name accordingly

@body = @MailBody;

END

 

END


Blocking alert from SQL Server

Below mentioned script will send the blocking queries which are blocked for more than 5 minutes and we need to create job for below script to run every 10 minutes

SET NOCOUNT ON

DECLARE @xml nvarchar(max)

SELECT @xml = Cast((SELECT @@SERVICENAME AS 'td','',b.session_id AS 'td',

'',

(b.wait_duration_ms/1000)/60 AS 'td',

'',

b.wait_type AS 'td',

'',

b.blocking_session_id AS 'td',

'',

t.text AS 'td'

FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id

OUTER APPLY

sys.dm_exec_sql_text(sql_handle) t

WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>300000

FOR xml path('tr'), elements) AS NVARCHAR(max))

Declare @body nvarchar(max)

SET @body =

'<html>

<head>

<style>

table, th, td

 {

border: 1px solid black;

border-collapse: collapse;

text-align: center;

}

</style>

</head>

<body>

<H2>

Blocking queries

</H2>

<table>

 <tr>

<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>

 <th> Blocking Session ID</th><th>Query waiting to execute</th>

</tr>'

SET @body = @body + @xml + '

</table>

</body>

</html>'

if(@xml is not null)

BEGIN

EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'sqlmail',

@body = @body,

@body_format ='html',

@recipients = 'bajeyudu@sqldbanow.com',

@copy_recipients ='ajay@sqldbanow.com', -- list of Email recipients

@subject = 'Blocking queries Alert-SQLDBANOWDB01';

END

SET NOCOUNT OFF


If you are not able to shrink tempdb execute below query and try again

  -----If you are not able to shrink tempdb execute below query and try again

USE TempDB

GO

--Use below query for clearing plan cache in the server.

DBCC FREEPROCCACHE

go

--Use below query for clearing buffers from bufferpool.

DBCC DROPCLEANBUFFERS

go

-- Use below query, this will Releases all unused cache entries from all caches.

DBCC FREESYSTEMCACHE ('ALL')

go

Before Database refresh script out user permissions

--Before Database refresh script out user permissions

DECLARE 


    @sql VARCHAR(2048)


    ,@sort INT 

DECLARE tmp CURSOR FOR


/*********************************************/


/*********   DB CONTEXT STATEMENT    *********/


/*********************************************/


SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],


        1 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],


        1 AS [-- RESULT ORDER HOLDER --]




UNION




SELECT '' AS [-- SQL STATEMENTS --],


        2 AS [-- RESULT ORDER HOLDER --]


UNION


/*********************************************/


/*********     DB USER CREATION      *********/


/*********************************************/




SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],


        3 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],


        4 AS [-- RESULT ORDER HOLDER --]


FROM    sys.database_principals AS rm


WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups




UNION


/*********************************************/


/*********    DB ROLE PERMISSIONS    *********/


/*********************************************/


SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],


        5 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  'EXEC sp_addrolemember @rolename ='


    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],


        6 AS [-- RESULT ORDER HOLDER --]


FROM    sys.database_role_members AS rm


WHERE   USER_NAME(rm.member_principal_id) IN (  


                                                --get user names on the database


                                                SELECT [name]


                                                FROM sys.database_principals


                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas


                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group


                                              )


--ORDER BY rm.role_principal_id ASC


UNION


SELECT '' AS [-- SQL STATEMENTS --],


        7 AS [-- RESULT ORDER HOLDER --]

UNION


/*********************************************/


/*********  OBJECT LEVEL PERMISSIONS *********/


/*********************************************/


SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],


        8 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  CASE 


            WHEN perm.state <> 'W' THEN perm.state_desc 


            ELSE 'GRANT'


        END


        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects


        + CASE


                WHEN cl.column_id IS NULL THEN SPACE(0)


                ELSE '(' + QUOTENAME(cl.name) + ')'


          END


        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default


        + CASE 


                WHEN perm.state <> 'W' THEN SPACE(0)


                ELSE SPACE(1) + 'WITH GRANT OPTION'


          END


            AS [-- SQL STATEMENTS --],


        9 AS [-- RESULT ORDER HOLDER --]


FROM    


    sys.database_permissions AS perm


        INNER JOIN


    sys.objects AS obj


            ON perm.major_id = obj.[object_id]


        INNER JOIN


    sys.database_principals AS usr


            ON perm.grantee_principal_id = usr.principal_id


        LEFT JOIN


    sys.columns AS cl


            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id


--WHERE usr.name = @OldUser


--ORDER BY perm.permission_name ASC, perm.state_desc ASC


UNION


SELECT '' AS [-- SQL STATEMENTS --],


    10 AS [-- RESULT ORDER HOLDER --]



UNION


/*********************************************/


/*********    DB LEVEL PERMISSIONS   *********/


/*********************************************/


SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],


        11 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  CASE 


            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option


            ELSE 'GRANT'


        END


    + SPACE(1) + perm.permission_name --CONNECT, etc


    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>


    + CASE 


            WHEN perm.state <> 'W' THEN SPACE(0) 


            ELSE SPACE(1) + 'WITH GRANT OPTION' 


      END


        AS [-- SQL STATEMENTS --],


        12 AS [-- RESULT ORDER HOLDER --]


FROM    sys.database_permissions AS perm


    INNER JOIN


    sys.database_principals AS usr


    ON perm.grantee_principal_id = usr.principal_id


--WHERE usr.name = @OldUser


WHERE   [perm].[major_id] = 0


    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas


    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group


UNION


SELECT '' AS [-- SQL STATEMENTS --],


        13 AS [-- RESULT ORDER HOLDER --]

UNION 


SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],


        14 AS [-- RESULT ORDER HOLDER --]


UNION


SELECT  CASE


            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option


            ELSE 'GRANT'


            END


                + SPACE(1) + perm.permission_name --CONNECT, etc


                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>


                + QUOTENAME(SCHEMA_NAME(major_id))


                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default


                + CASE


                    WHEN perm.state <> 'W' THEN SPACE(0)


                    ELSE SPACE(1) + 'WITH GRANT OPTION'


                    END


            AS [-- SQL STATEMENTS --],


        15 AS [-- RESULT ORDER HOLDER --]


from sys.database_permissions AS perm


    inner join sys.schemas s


        on perm.major_id = s.schema_id


    inner join sys.database_principals dbprin


        on perm.grantee_principal_id = dbprin.principal_id


WHERE class = 3 --class 3 = schema


ORDER BY [-- RESULT ORDER HOLDER --]


OPEN tmp


FETCH NEXT FROM tmp INTO @sql, @sort


WHILE @@FETCH_STATUS = 0


BEGIN


        PRINT @sql


        FETCH NEXT FROM tmp INTO @sql, @sort    

END

CLOSE tmp


DEALLOCATE tmp 

Known issues with Azure SQL Managed Instance

Known issues with Azure SQL Managed Instance 

Top 3 Table Row count report via email alert in SQL Server

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

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  

DECLARE @Max_Records TABLE (

[DB_name] [nvarchar](128) NULL,

[SchemaName] [sysname] NOT NULL,

[TableName] [sysname] NOT NULL,

[RowCounts] [bigint] NOT NULL,

[Used_MB] [numeric](36, 2) NULL,

[Unused_MB] [numeric](36, 2) NULL,

[Total_MB] [numeric](36, 2) NULL,

[Capture_Date] Date

)

SELECT @command = 'use [?]; 

SELECT Top 3

DB_Name(),s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,Getdate() As Capture_Date

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

GROUP BY t.Name, s.Name, p.Rows

ORDER BY CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) desc' 

INSERT INTO @Max_Records   

EXEC sp_MSForEachDB @command     

--Select * from @Max_Records

SET @xml = CAST(( SELECT DB.DB_name As 'td',' ',DB.SchemaName AS 'td' ,' ',DB.TableName as 'td' ,' ',DB.RowCounts as 'td',' ',Db.Used_MB as 'td',  

' ',DB.Unused_MB as 'td' ,' ',DB.Total_MB as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @Max_Records  DB  Order by DB.RowCounts Desc

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

SET @body ='<html><body><H3>Top 3 Table Row count</H3>  

<table border = 1>   

<th> Database Name </th><th> Schema  Name </th> <th> TableName </th> <th> RowCounts</th> <th> Used_MB </th> <th> Unused_MB </th>  

<td bgcolor=#F0E68C><b>Total_MB(%)<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 + ': Top 3 Table Row count '  

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

@subject = @cmd

Log_Backup-Status_report via Email alert in SQL Server

 Set nocount ON

Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000),@Bkupstatus Varchar (500)



If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#DBBackup


If  OBJECT_ID('tempdb..#Log') is not NULL

     Drop table tempdb..#Log

     


Create Table #DBBackup (Server varchar(500),DB varchar(1000), Backup_Status varchar(1000))

Create Table #Log (Server varchar(500),DB varchar(1000),Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)


Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' and recovery_model_desc='FULL'  order by Name

Open cur

fetch next from cur into @DBName

while @@fetch_status=0

     Begin

Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')

Select @replicaid=replica_id from sys.databases where name=@DBName

If   @replicaid is NULL And @dbStatus='ONLINE' 

       Begin

    insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", ''

       End  

  Else If (@dbIs='READ_WRITE' or @dbIs IS NULL ) And @dbStatus='ONLINE' AND @replicaid is Not NULL

         Begin

   insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", ''

         End

   fetch next from cur into @DBName

      End

Close Cur

Deallocate Cur


insert into #Log

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'l' 

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <5

order by 2 



Update #DBBackup

Set Backup_Status='FAILED'

where  DB not in (Select DB from #Log)


Update #DBBackup

Set Backup_Status='Completed'

where Backup_Status<>'FAILED'


--SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',L.Last_Backup as 'td','',L.Backup_Size as 'td',

--'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join  #Log L on DB.DB=L.DB


--SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) as 'td','',L.Backup_Size as 'td',

--'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join  #Log L on DB.DB=L.DB where DB.Backup_Status='FAILED'

Select @Bkupstatus=Backup_Status from #DBBackup

If @Bkupstatus='FAILED'

Begin

SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',convert(varchar(20),L.Last_Backup,120) as 'td','',L.Backup_Size as 'td',

'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join  #Log L on DB.DB=L.DB where DB.Backup_Status='FAILED'

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


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

<table border = 1> 

<tr>

<th> Server Name </th> <th> Database Name </th> <th> Backup Status </th> <th> Last_Backup </th><th> Backup_Size (MB) </th>  <th> Compressed_Backup_Size (MB) </th> </tr>'    

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

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

Set @cmd= @@SERVERNAME + ': Log Database Backup '

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

@subject = @cmd

End

DROP TABLE [#DBBackup]

DROP TABLE [#Log]

Full Database Backup status report via email alert in SQL Server

 Set nocount ON

Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500),@cmd Varchar (1000),@profile_name Varchar(1000)


If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#DBBackup


If  OBJECT_ID('tempdb..#Log') is not NULL

     Drop table tempdb..#Log

     


Create Table #DBBackup (Server varchar(500),DB varchar(1000), Backup_Status varchar(1000))

Create Table #Log (Server varchar(500),DB varchar(1000),Last_Backup Date,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)


Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' order by Name

Open cur

fetch next from cur into @DBName

while @@fetch_status=0

     Begin

Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')

Select @replicaid=replica_id from sys.databases where name=@DBName

If   @replicaid is NULL And @dbStatus='ONLINE' 

       Begin

    insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''

       End  

  Else If (@dbIs='READ_WRITE' or @dbIs IS NULL ) And @dbStatus='ONLINE' AND @replicaid is Not NULL

         Begin

   insert into #DBBackup Select Upper(@@SERVERNAME) Server, Upper(@DBName) Database, ''

         End

   fetch next from cur into @DBName

      End

Close Cur

Deallocate Cur


insert into #Log

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size1048576) AS Backup_Size ,(Compressed_Backup_Size1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'd' 

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) 120

order by 2 



Update #DBBackup

Set Backup_Status='FAILED'

where  DB not in (Select DB from #Log)


Update #DBBackup

Set Backup_Status='Completed'

where Backup_Status'FAILED'


SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Backup_Status as 'td' ,'',L.Last_Backup as 'td','',L.Backup_Size as 'td',

'',L.Compressed_Backup_Size as td from #DBBackup DB Left Outer join  #Log L on DB.DB=L.DB

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


SET @body ='htmlbodyH3Full Database Backup status H3

table border = 1 

tr

th Server Name th th Database Name th th Backup Status th th Last_Backup th th Backup_Size (MB) th th Compressed_Backup_Size (MB) th tr'    

 

SET @body = @body + @xml +'tablebodyhtml'


Set @cmd= @@SERVERNAME + ' Full Database Backup '


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

@subject = @cmd


DROP TABLE [#DBBackup]

DROP TABLE [#Log]

Database Index Status report in SQL Server via email alert

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

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  


DECLARE @Index_details TABLE (

 Database_name sysname,

 Objectname sysname,

 Objectid Int,

 Indexid Int,

 partitionnum Int,

 frag decimal(30,2),

 Page_Count Int,

 Capture_Date Date)


Insert Into @Index_details

EXEC sp_MSForEachDB

'use [?]; SELECT DB_Name() As Database_name,Object_name (OBJECT_ID) As Object_name, [object_id] AS objectid ,index_id AS indexid,partition_number AS partitionnum,avg_fragmentation_in_percent AS frag, page_count,getdate()

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25 -- Ignore small tables

Order by avg_fragmentation_in_percent Desc,page_count Desc'


--Select * from @Index_details



SET @xml = CAST(( SELECT ID.Database_name As 'td',' ',ID.Objectname AS 'td' ,' ',ID.Objectid as 'td' ,' ',ID.Indexid 'td',' ',ID.partitionnum as 'td',  

' ',ID.frag as 'td' ,' ',ID.Page_Count as 'td',' ', convert(varchar(20), ID.Capture_Date,120) as 'td'  from @Index_details  ID  

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


SET @body ='<html><body><H3>Database Index Status </H3>  

<table border = 1>   

<th> Database Name </th><th> Objectname </th> <th> Objectid </th> <th> Indexid </th> <th> Partition </th> <th> frag </th>  

<td bgcolor=#F0E68C><b>Pagecount<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 Index 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 =  'IT.ESA-DBA@sqldbanow.com', -- replace with your email address  

@subject = @cmd

Current Disk Free Space in SQL Server via email alert

 DECLARE @cmd Varchar (1000),@profile_name Varchar(1000)  

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  


Declare @Disk_Size Table

(Drive Nvarchar(Max),Free_Space_MB decimal(30,2))

Insert Into @Disk_Size

Exec Xp_fixeddrives


--Select * from @Disk_Size


SET @xml = CAST(( SELECT DS.Drive As 'td',' ',DS.Free_Space_MB AS 'td' ,' ', convert(varchar(20),getdate(),120) as 'td'  from @Disk_Size  DS  

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


SET @body ='<html><body><H3>Current Disk Free Space </H3>  

<table border = 1>   

<th> Drive Name </th><td bgcolor=#F0E68C><b>Free_Space_MB (%)<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 + ': Current Disk Free 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 = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address  

@subject = @cmd

Backup Status Report in SQL Server via Email

 Set nocount ON

Declare @DBName Varchar (500), @dbIs SQL_Variant, @dbStatus SQL_Variant,@replicaid Varchar(500)

Declare @cmd Varchar (1000),@profile_name Varchar(1000), @description Varchar (500)


If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#DBBackup


If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#FullBackup


If  OBJECT_ID('tempdb..#DBBackup') is not NULL

     Drop table tempdb..#DiffBackup


If  OBJECT_ID('tempdb..#Log') is not NULL

     Drop table tempdb..#Log

     


Create Table #DBBackup (Server varchar(500),DB varchar(1000),Full_Backup_Status varchar(1000),Diff_Backup_Status varchar(1000), Log_Backup_Status varchar(1000))

Create Table #FullBackup (Server varchar(500),DB varchar(1000),Full_Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

Create Table #DiffBackup (Server varchar(500),DB varchar(1000),Diff_Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

Create Table #Log (Server varchar(500),DB varchar(1000),Log_Last_Backup Datetime,Time_Since_Minute int,Backup_Size numeric,Compressed_Backup_Size Numeric)

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)


Declare cur CURSOR for Select Name from sys.databases where name not in ('tempdb') and state_desc='ONLINE' and recovery_model_desc='FULL'  order by Name

Open cur

fetch next from cur into @DBName

while @@fetch_status=0

     Begin

Select @dbIs=Databasepropertyex (@DBName,'Updateability'),@dbStatus=Databasepropertyex (@DBName,'Status')

Select @replicaid=replica_id from sys.databases where name=@DBName

Set @description= ''

SELECT @description =  hars.role_desc

FROM sys.DATABASES d

INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id

WHERE database_id = DB_ID(@DBName)

   

    if (@description<>'SECONDARY' and @dbIs='READ_WRITE')

    Begin

    insert into #DBBackup Select Upper(@@SERVERNAME) "Server", Upper(@DBName) "Database", '','',''

    End  

fetch next from cur into @DBName

  End

Close Cur

Deallocate Cur


--Full backup Status--

If Datename(weekday,(getdate()))='Monday'

   Begin

insert into #FullBackup

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'd'  and is_copy_only=0

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <360

order by 2 

  End

else

   Begin

insert into #FullBackup

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'd'  and is_copy_only=0

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(Day, MAX(Backup_Finish_Date), Getdate()) <8

order by 2 

End

Update #DBBackup

Set Full_Backup_Status='**Need to backup verify**'

where  DB not in (Select DB from #FullBackup)


Update #DBBackup

Set Full_Backup_Status='Completed'

where Full_Backup_Status<>'**Need to backup verify**'


--Select * from #DBBackup


--Diff Backup Satatus -----

If Datename(weekday,(getdate()))='Monday'

   Begin

insert into #DiffBackup

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'I' 

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(DAY, MAX(Backup_Finish_Date), Getdate()) <3

order by 2 

    End

 Else

     Begin   

    insert into #DiffBackup

SELECT @@Servername 'Server', Database_Name, 

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Full_Last_Backup, 

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last,(Backup_Size/1048576) AS Backup_Size ,(Compressed_Backup_Size/1048576) AS Compressed_Backup_Size

FROM MSDB.dbo.BackupSet

WHERE Type = 'I' 

GROUP BY Database_Name,backup_size,compressed_backup_size

Having  DATEDIFF(MINUTE, MAX(Backup_Finish_Date), Getdate()) <180

order by 2 

      End



Update #DBBackup

Set Diff_Backup_Status='**Need to backup verify**'

where  DB not in (Select DB from #DiffBackup)


Update #DBBackup

Set Diff_Backup_Status='Completed'

where Diff_Backup_Status<>'**Need to backup verify**'



SET @xml = CAST(( SELECT DB.Server As 'td',' ',DB.DB AS 'td' ,'' ,DB.Full_Backup_Status as 'td' ,'',convert(varchar(20),F.Full_Last_Backup,120) as 'td','',

Diff_Backup_Status as 'td','',convert(varchar(20),D.Diff_Last_Backup,120) as td 

from #DBBackup DB Left Outer join #FullBackup F on DB.DB=F.DB  Left outer join #DiffBackup D on DB.DB=D.DB

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


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

<table border = 1> 

<tr>

<th> Server Name </th> <th> Database Name </th> <th> Full_Backup_Status </th> <th> Full_Last_Backup </th><th> Differential_Backup_Status </th><th> Differential_Last_Backup</th> </tr>'    

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


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


Set @cmd= @@SERVERNAME + ': Database Backup 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 =  'itesadba@sqldbanow.com', -- replace with your email address

@subject = @cmd


DROP TABLE [#DBBackup]

DROP TABLE [#FullBackup]

DROP TABLE [#DiffBackup]

DROP TABLE [#Log]