SQL Alert for long running queries I want to receive a mail when a query is running for more than x seconds. I know I can set up a job running on the database but, is there any way to do the same using SQL Alerts? What kind of alert should I use?
   Thanks.
If you have sp_WhoIsActive (great tool, can be downloaded here: http://whoisactive.com/) you can log the results to a table and then email out if the threshold you set for the duration is tripped. 

example:

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Load WhoIsActive To A Temp Table

IF OBJECT_ID('tempdb.dbo.#wia') IS NOT NULL DROP TABLE #wia

CREATE TABLE #wia ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

EXEC sp_WhoIsActive 
    @format_output = 1, 
    @destination_table = '#wia'

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Find Long Running Queries


IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #Temp

SELECT 
[dd hh:mm:ss.mss],
session_id,
CAST(sql_text AS VARCHAR(MAX)) AS sql_text,
login_name,
wait_info,
host_name,
database_name,
program_name,
start_time
INTO #Temp
FROM
#wia

WHERE
start_time &lt; DATEADD(HOUR,-8,GETDATE() ) -- CHANGE THIS TO WHATEVER YOU WANT

DECLARE @CNT INT = (SELECT COUNT(*) FROM #Temp )

IF @CNT &gt; 0 
BEGIN
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------



DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT [dd hh:mm:ss.mss] AS 'td','',session_id AS 'td','', [sql_text] AS 'td','', 
[login_name] AS 'td','',[wait_info] AS 'td','','',[host_name] AS 'td','',
[database_name] AS 'td','', [program_name] AS 'td','',start_time AS 'td'
FROM  #Temp 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))



SET @body ='&lt;html&gt;&lt;body&gt;&lt;H3&gt;Long Running Queries&lt;/H3&gt;
&lt;table border = 1&gt; 
&lt;tr&gt;
&lt;th&gt; dd hh:mm:ss.mss &lt;/th&gt; &lt;th&gt; session_id &lt;/th&gt; &lt;th&gt; sql_text &lt;/th&gt; &lt;th&gt; login_name &lt;/th&gt;
&lt;th&gt; wait_info &lt;/th&gt; &lt;th&gt; host_name &lt;/th&gt; &lt;th&gt; database_name &lt;/th&gt;
&lt;th&gt; program_name &lt;/th&gt; &lt;th&gt; start_time &lt;/th&gt;

&lt;/tr&gt;'    


SET @body = @body + @xml +'&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXX', -- replace with your profile
@body = @body,
@importance = 'HIGH',
@body_format ='HTML',
@recipients = 'XXXXX', -- replace with your email address
@subject = 'Longing Running Queries On &lt;SERVER_NAME&gt;' ;


DROP TABLE #Temp



END