查詢SQL Server ERROR LOG

/*查詢SQL Server ERROR LOG,18456 (帳號密碼錯誤), 18487(密碼過期),錯3次以上,
可用StartTime籂選區間條件,可替換ERROR查詢不同錯誤,可塞入Table自動發信*/

DECLARE @path varchar(max)
DECLARE @s1 varchar(max)
SELECT @path = path FROM sys.traces
SELECT HostName , LoginName, ApplicationName, StartTime, ServerName, Error, TextData
FROM fn_trace_gettable(@path, default)
WHERE Error IN ( 18456, 18487 )
AND LoginName IN (
SELECT LoginName
FROM fn_trace_gettable(@path, default)
WHERE Error IN ( 18456, 18487 )
GROUP BY LoginName
HAVING COUNT(*) > 2 )

/*未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。請改用擴充事件。*/

查詢鎖定及被鎖定Query

WITH SessionBlock AS
(
SELECT er.session_id, er.Blocking_session_id, 
er.sql_handle AS [si_sql_handle], 
er.plan_handle AS [si_plan_handle], 
er.statement_start_offset AS [si_statement_start_offset], 
er.statement_end_offset AS [si_statement_end_offset], 
ec.most_recent_sql_handle AS [bsi_sql_handle], 
es.last_request_start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es 
    ON er.Blocking_session_id = es.session_id
INNER JOIN sys.dm_exec_connections ec 
    ON er.Blocking_session_id = ec.session_id
WHERE er.blocking_session_id > 50
)
SELECT session_id, Blocking_session_id, 
SUBSTRING (sist.TEXT, (si_statement_start_offset/2)+1,
((CASE si_statement_end_offset WHEN -1 THEN datalength(sist.TEXT)  
 ELSE si_statement_end_offset END - si_statement_start_offset)/2) + 1 ) AS [si_query],
bsist.TEXT AS [bsi_query],
sb.last_request_start_time
FROM SessionBlock sb
CROSS APPLY sys.dm_exec_sql_text(sb.si_sql_handle) AS sist
CROSS APPLY sys.dm_exec_sql_text(sb.bsi_sql_handle) AS bsist