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

No comments:

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]

No comments:

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]

No comments:

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

No comments:

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

No comments:
Subscribe to: Posts (Atom)