Error Log를 검색해보기

IF OBJECT_ID('FN_ERRORLOG_DIR') IS NOT NULL

       DROP FUNCTION FN_ERRORLOG_DIR
GO 

CREATE FUNCTION DBO.FN_ERRORLOG_DIR()

RETURNS NVARCHAR(4000)

AS

/**************************************************

** ERROR LOG FOLDER가쌓이는곳은어딘가?

**************************************************/

BEGIN

       DECLARE @RC INT,

       @DIR NVARCHAR(4000)

 

       EXEC @RC =  MASTER.DBO.XP_INSTANCE_REGREAD N'HKEY_LOCAL_MACHINE'

                                               ,N'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\PARAMETERS'

                                              ,N'SQLARG1', @DIR OUTPUT, 'NO_OUTPUT'

       RETURN RIGHT(@DIR, LEN(@DIR)-2)

      

END

GO

 

 

 

IF OBJECT_ID('SP_LOG_FIND') IS NOT NULL

       DROP PROC SP_LOG_FIND

GO

 

 

CREATE PROC SP_LOG_FIND (

       @LF VARCHAR(100)= ''

)     

AS

/**************************************************

** 찾아라

**************************************************/

BEGIN

SET NOCOUNT ON

       DECLARE @CMD VARCHAR(1000)

       DECLARE @ELOG_DIR VARCHAR(1000)

       SELECT @ELOG_DIR = DBO.FN_ERRORLOG_DIR()

       IF @LF = '' BEGIN 

             PRINT 'EXAMPLE '

             PRINT 'EXEC SP_LOG_FIND AAA'

             RETURN

       END

       SET @CMD = 'FINDSTR /I /C:"'+@LF+'" "' + @ELOG_DIR + '*"'

       EXEC XP_CMDSHELL @CMD

 

END

GO