April 1, 2015 at 4:23 am
Hi,
I've been looking at using a method of alerting for a SQL Express Instance. I appreciate you can't normally do this as there is no Agent, but as the edition still comes with all the SP's and mail I've managed to set up everything I need to, with a couple of registry amendments!
The one thing that isn't happening is that msdb..sysalerts doesn't get updated when say a Sev 14 bad login occurs, which in turn is set to fire off a notification. So for example, if I run a Profiler trace on a Standard Edition instance with the Agent running I can see that when an alert is breached (ie Sev 14 in my instance) a statement runs that I assume looks at the Windows Event Log (?) and runs...
exec sp_executesql N'UPDATE msdb.dbo.sysalerts SET last_occurrence_date etc...the notification is then triggered from that.
However for Express this update doesn't run when the alert is breached. Is it because the update statement is something being fired from the Agent service (which of course isn't available in my Express instance)? Can I somehow replicate that manually though without the Agent so I can do something for Express? I was thinking that the solution maybe finding a piece of code I can use to trawl the Event Log looking for these alerts and therefore population the msdb..sysalerts table?
Thanks for any input.
Simon
April 4, 2015 at 2:43 am
Quick suggestion, create a procedure that executes sp_readerrorlog and filter/search the output, then use sqlcmd and a windows scheduled task to execute the procedure.
😎
DECLARE @LOG TABLE
(
LogDate DATETIME NOT NULL
,ProcessInfo SYSNAME NOT NULL
,[Text] NVARCHAR(4000) NOT NULL
);
INSERT INTO @LOG(LogDate,ProcessInfo,[Text])
EXEC sp_readerrorlog 0,NULL,NULL;
SELECT
L.LogDate
,L.ProcessInfo
,L.[Text]
FROM @LOG L
WHERE L.Text LIKE N'%ERROR%'
April 7, 2015 at 2:44 am
Yes thanks for thta, I'd actually done something similar already. Was hoping for something that would alert me instantly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply