SQL Server DBA and Azure SQL Insights

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

Automating Top 3 Table Row Count Reports in SQL Server

As database administrators, monitoring table sizes and row counts is crucial for performance tuning, space management, and optimizing database resources. One of the ways to accomplish this is by generating a report that lists the top 3 tables with the highest row counts in each database, along with space usage details.

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>

<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

Automating SQL Server Log Backup Status Monitoring and Email Alerts

In SQL Server environments, ensuring timely backups and monitoring their status is essential for maintaining database integrity. Here's a simple approach to automate the process of checking backup statuses and sending email notifications if there are any failures.

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>

<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

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>

<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>

<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

SQL Server DBA and Azure SQL Insights

Pages

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

Automating SQL Server Log Backup Status Monitoring and Email Alerts

Full Database Backup status report via email alert in SQL Server

Database Index Status report in SQL Server via email alert

Current Disk Free Space in SQL Server via email alert

Report Abuse

My Blog List