SQL SERVER – ReadErrorLog

Using xp_ReadErrorLog in SQL Server 

sp_ReadErrorLog : uses the extended stored procedure,the interesting part starts now with the parameters  1 reads the error log number passed to it, where the default "0" reads the current log.

Example :

xp_ReadErrorLog

exec master..xp_readerrorlog 0, 1

 LogDate                  ProcessInfo  Text

Important  Notes :

            ----Reads the current sql server log

                                   exec master..xp_readerrorlog

                                   exec master..xp_readerrorlog 0, 1

        - Reads SQL Server error log from ERRORLOG.1 file

                                    EXEC xp_ReadErrorLog 1

            --Reads the previous sql server log

                                  exec master..xp_readerrorlog 1, 1

             --Reads the current sql server agent log

                                 exec master..xp_readerrorlog 0, 2

             --Reads the Login  Failed log

                               exec master..xp_ReadErrorLog 0, 1, 'Failed', 'login'

            - Reads current SQL Server error log with text 'Failed'

                EXEC xp_ReadErrorLog 0, 1, 'Failed'

    - Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ from 01-Nov-2012

            EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL

- Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ between 01-Nov-2012 and 30-Nov-2012

    EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'

  EXEC xp_ReadErrorLog 0, 1, N'Login', N'failed', 'Aug  4 2013  3:51AM', 'Aug  4 2013  4:51AM'

- Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'

For example, to be alerted when the DBCC trace flag has been enabled in the last hour, replace the procedure with:

EXEC xp_ReadErrorLog  0, 1, N'DBCC', N'TRACEON', @starttime, @endtime;

2008-03-04 12:11:01.450  Server       Microsoft SQL Server 2005 - 9.00.3159.00         (Intel X86)   Mar 23 2007 16:15:11

2008-03-04 12:11:01.500  Server   (c) 2005 Microsoft Corporation.

2008-03-04 12:11:01.500  Server       All rights reserved. 

2008-03-04 12:11:01.500 Server                      Server process ID is 1284.

2008-03-04 12:11:01.500  Server       Authentication mode is MIXED.

2008-03-04 12:11:01.510  Server  Logging SQL Server messages in file                                  'D:\SRVAPPS\MSSQL.1\MSSQL\LOG\ERRORLOG

Parameter (2). By passing a value of 2, the SQL server Agent log is read  example: xp_ReadErrorLog 0, 2 reads the current SQL server Agent log

xp_ReadErrorLog 0,2

That contain "failed" and "login" 

xp_ReadErrorLog 0, 1, 'Failed', 'login'

Parameters defined

• • Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log. The extended stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error logs or SQL server Agent logs exist and when they were created. NOTE: extended stored procedure xp_enumerrorlogs parameter (1) works just like xp_ReadErrorLog parameter (2). A value of 1 shows available SQL error logs and a value of 2 shows Agent logs. The default value is 0.

  • Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.

  • Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.

  •  Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.

To locate any problems during setup, check the sqlstp.log, setup.log and errorlog files.