USE [FPAuth]
GO
/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step3] Script Date: 8/20/2021 8:06:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step3]
AS
BEGIN
/*
Author:Naresh DBA
Date: 04/01/2016
Purpose:Send Index fragmentation details every 1 hour
Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30
*/
DECLARE
@Document NVARCHAR(MAX) ,
@DocumentHead NVARCHAR(MAX) ,
@DocumentTail NVARCHAR(MAX) ,
@TableHead NVARCHAR(MAX) ,
@TableBody NVARCHAR(MAX) ,
@TableTail NVARCHAR(MAX) ,
@page_count_minimum SMALLINT ,
@fragmentation_minimum INT;
SET @page_count_minimum =1000;
SET @fragmentation_minimum = 30;
SET @DocumentHead = N'<html><head>' + N'<style>' + N'th, td' + N'{ '
+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'
+ N'<h3>'
+ N'Below are the Indexes in FPAuth Database with Average Fragmentation above 30 Percent'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'
+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'
+ N' <th>PageCount</th>' + N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )
% 2 AS [TRRow] ,
sys.objects.name AS [TD] ,
sys.indexes.name AS [TD] ,
CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,
page_count AS [TD]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL,
'LIMITED')
INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND objects.is_ms_shipped = 0
AND sys.dm_db_index_physical_stats.index_id > 0
--AND sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016
AND page_count > @page_count_minimum
ORDER BY avg_fragmentation_in_percent DESC
FOR
XML RAW('tr') ,
ELEMENTS
);
IF @TableBody IS NULL
BEGIN
RETURN;
END;
ELSE
BEGIN
SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));
SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');
SET @TableBody = REPLACE(@TableBody, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');
SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients = 'abc@xyz.com',
@subject = 'Average Index Fragmentation with above 30 Percent',
@body = @Document,
@body_format = 'HTML';
END
END
