February 13, 2013 at 11:39 pm
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
February 14, 2013 at 9:22 am
Why can't you just change your IF statement?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply