This metric returns the number of ‘login failed’ error messages found in the SQL Server error log file in the last hour.
The T-SQL for this metric uses the undocumented stored procedure: xp_readerrorlog. Once you’ve been alerted to login failures, you can find out why they occurred by running the procedure against the SQL Server error log file and searching for text similar to this:
2013-07-31 04:56:08.34 Logon Error: 18456, Severity: 14, State: 5. 2013-07-31 04:56:08.34 Logon Login failed for user '<user name>'. [CLIENT: <ip address>
The error state corresponds to a specific reason for failure, for example, state 5 means the user entered an invalid userid. For details of each state, see: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx.
You can create additional custom metrics that read different messages in the SQL Server or SQL Agent error log file by reusing an edited version of this T-SQL. You’ll need to update the parameters for the procedure:
EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;
For example, to be alerted when the DBCC trace flag has been enabled in the last hour, replace the procedure with:
EXEC sys.xp_ReadErrorLog 0, 1, N'DBCC', N'TRACEON', @starttime, @endtime;
Note: There is a known issue with using xp_readerrorlog in SQL Server versions earlier than 2008 R2. The command may stop responding during execution and cause CPU usage to increase to 100%. For more details and links to fixes from Microsoft, go to http://support.microsoft.com/kb/973524.Failed SQL Server logins,