How do we read the SQL Server error log?

Well there is an undocumented system procedure to do it sp_readerrorlog.This procedure reads from the disk using an extended stored procedure. To use it you need to be in the security admin group. Or you will get -

Msg 15003, Level 16, State 1, Line 1

Only members of the securityadmin role can execute this stored procedure.

The parameters for sp_readerrorlog are -

@p1 = Log file to read, this is the number, if you specify 0 you will get the current file ERRORLOG 
@p2 = Type of log to read, SQL Server (1) or SQL Agent (2) 
@p3 = Some text to find in the log entries 
@p4 = Some more text to find in the log entries (essentially the result is @p3 AND @p4)

All of the above stated parameters are optional and the default values for each are –

@p1 = 0 (ZERO); will get the current file ERRORLOG 
@p2 = SQL Server (1) 
@p3 = NULL
@p4 = NULL

Using this information we can do the following

EXEC sp_readerrorlog 0, 1, 'SEARCH TEXT 1',' SEARCH TEXT 2'

This only reads one file. If the files have been cycled or the server restarted we will miss our change. We can use a second undocumented procedure to get the list of log files - sp_enumerrorlogs

This takes 1 optional parameter

@p1 = Type of log to read, SQL Server (1) or SQL Agent (2). Same as @p2 for sp_readerrorlog. This defaults on 1 if not specified.

Using these together we can go through the logs looking for our changes.