sp_readerrorlog (xp_readerrorlog)

sp_readerrorlog

CREATE PROC [sys].[sp_readerrorlog](     @p1     INT = 0,     @p2     INT = NULL,     @p3     VARCHAR(255) = NULL,     @p4     VARCHAR(255) = NULL)  AS  BEGIN     IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)     BEGIN        RAISERROR(15003,-1,-1, N'securityadmin')        RETURN (1)     END          IF (@p2 IS NULL)         EXEC sys.xp_readerrorlog @p1     ELSE         EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4  END

xp_readerrorlog

Even though sp_readerrolog accepts only 4 parameters, the extended stored procedure accepts at least 7 parameters.

If this extended stored procedure is called directly the parameters are as follows:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time  
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending

EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc'  EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'

- 날짜 조회 방법

exec master.dbo.xp_readerrorlog 0, 1, null, null, '2010-08-15', '2010-08-17', N'asc'

출처 : http://www.mssqltips.com/tip.asp?tip=1476