Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email Alert for VLF_count > 50? Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 11:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:55 PM
Points: 1,076, Visits: 3,054
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
Post #1419824
Posted Thursday, February 14, 2013 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,676, Visits: 6,488
Why can't you just change your IF statement?

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1420146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse