Email Alert for VLF_count > 50?

  • Hi,

    I have modified this script for getting email alert just VLF count information, also working fine and received email..

    I want to setting and receive alert when VLF count > 50 reached, could anyone modify this script as per requirement.

    Create Table #stage(

    FileID int

    , FileSize bigint

    , StartOffset bigint

    , FSeqNo bigint

    , [Status] bigint

    , Parity bigint

    , CreateLSN numeric(38)

    );

    Create Table #results(

    Database_Name sysname

    , VLF_count int

    );

    Exec sp_msforeachdb N'Use ?;

    Insert Into #stage

    Exec sp_executeSQL N''DBCC LogInfo(?)'';

    Insert Into #results

    Select DB_Name(), Count(*)

    From #stage;

    Truncate Table #stage;'

    declare @VLFcount float

    select @VLFcount=VLF_Count from #results

    if (@VLFcount < 50)

    begin

    declare @strsubject varchar(100)

    select @strsubject='Check VLF_Count usage on ' + @@SERVERNAME

    declare @tableHTML nvarchar(max);

    set @tableHTML =

    N'<H1>VLF Information - ' + @@SERVERNAME +'</H1>' +

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

    N'<tr><th>Database_Name</th><th>VLF_Count MB</th></tr>' +

    --N'<th>% Memory Free</th></tr>' +

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

    td = [VLF_Count], ''

    FROM #results

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail

    @from_address='test@test.com',

    @recipients='test@test.com',

    @subject = @strsubject,

    @body = @tableHTML,

    @body_format = 'HTML' ,

    @profile_name='dbmail'

    end

    Drop Table #stage;

    Drop Table #results;

    rgds

    ananda

  • Why can't you just change your IF statement?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply