Friday, August 20, 2021

alert index fragmentation

 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 

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...