asked
SQL Server alerts not working
I have created an alert under sql server agent for longest running transaction. The alert runs a job in response and the job runs the below query and stores the results in one of the databases. The job also alerts the DBA through e-mail. select sp.spid , sp.status , sp.loginame , sp.hostname , sp.blocked , db_name(sp.dbid) database_name , er.wait_type , er.cpu_time , er.total_elapsed_time , er.reads , er.writes , er.logical_reads , st.text , sp.login_time , sp.last_batch , getdate() from sys.sysprocesses as sp join sys.dm_exec_requests as er on ( sp.spid = er.session_id ) cross apply sys.dm_exec_sql_text(er.sql_handle) as st The problem is that the alert threshold which is supposed to be in seconds is not working properly. I have set the alert to trigger when a transaction is running longer than 20 mins but when i try to run a huge SELECT statement, the alert does not get triggered. The alert gets triggered only when a BULK insert or an update runs longer than normal. Also when i set the threshold to "becomes equal to 20 mins" the alerts triggers every second, which is not normal as there are no processes running longer than 20 mins. When i set the threshold to "raises above 20 mins", alert gets triggered only when a BULK insert or an update runs longer than normal not for a SELECT statement. Can some one help me to have this alert trigger only when any transaction is running longer than 20 mins?
It sounds like you have used the counter 'Longest Transaction Running Time' The documentation suggests that this will only pick up explicit transactions or implicit modifications - so your long running Select will not fire. See also
http://serverfault.com/questions/165266/what-sql-server-query-will-set-perfmon-counter-longest-transaction-running-time
It's a kind of gut-feel, read-between-the-lines, that's-what-the-evidence-suggests conclusion!
That's what I was thinking it sounded like but I couldn't find anything in the documentation.
9 People are following this question.
