xp_readerrorlog : 에러 로그 확인 할 때

출처 및 참고

http://blog.sqltechie.com/2011/03/xpreaderrorlog-parameter-detail.html

https://sqlandme.com/2012/01/25/sql-server-reading-errorlog-with-xp_readerrorlog/

https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

 

 

EXEC xp_ReadErrorLog    <LogNumber>, <LogType>,

                        <SearchTerm1>, <SearchTerm2>,

                        <StartDate>, <EndDate>, <SortOrder>

 

To read error logs in SQL Server using T-SQL you can use extended stored procedure xp_ReadErrorLog to read SQL Server and SQL Server Agent error logs. xp_ReadErrorLog has seven parameters that can be used to filter error logs.

 

 

ex)

EXEC xp_readerrorlog 0, 1, null, null,  '2016-08-07', '2016-08-08', 'asc'

 

The parameter values can be as follows:

Parameter	Values
<LogNumber>	Log number 0, 1, 2 … For example 0 returns current log. 2 returns logs from ERRORLOG.2
<LogType>	1 – Reads SQL Server error logs, 2 – Reads SQL Server Agent error logs
<SearchTerm1>	Search Term for Text Column
<SearchTerm2>	Search Term for Text Column * When both search terms are specified, it only returns lines containing both terms
<StartDate>	Start reading logs from specified date
<EndDate>	Reads logs till this date
<SortOrder>	ASC – Ascending or DESC – Descending

You can use the stored procedure as:

 

EXEC xp_ReadErrorLog

– Reads current SQL Server error log

 

 

 

Below are some more examples of xp_ReadErrorLog:

 

EXEC xp_ReadErrorLog 1

– Reads SQL Server error log from ERRORLOG.1 file

 

EXEC xp_ReadErrorLog 0, 1

– Reads current SQL Server error log

 

EXEC xp_ReadErrorLog 0, 2

– Reads current SQL Server Agent error log

 

EXEC xp_ReadErrorLog 0, 1, 'Failed'

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

 

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

– Reads current SQL Server error log with text ‘Failed’ AND 'Login'

 

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

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

 

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

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

 

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

– Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012

 

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

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