SQL Server – How to see the SQL Server ERROR Log.

SQL server stored the error log, which occurred in run time. Some time we need to see the error log to get the solution for Client and generally look into the stored procedure and need to see the error log to get instant solution of the problem

After login we can see the error log in SQLAGENT   it is the very simple way to see the error log but some time DBA team don’t gives the direct access then we can also see the error log using TSQL for analyse error and it reason.

In the sql server have inbuilt stored procedure sp_readerrorlog. Using this SP, we can see the error log for the specified time.

Code of System Stored procedure 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

Parameter of SP:-

• Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
• Log file type: 1 or NULL = error log, 2 = SQL Agent log
• Search string 1: String one you want to search for
• Search string 2: String two you want to search for to further refine the results

Note:- We can run this system procedure without any parameter.

Please see below for readymade script to get error log.

Scripts1: To find the Error log in SQL Server 2000

DECLARE @HOURS INT

SET @HOURS = 24 -- it is the time of error log

-- creating the temp table to store the error log

CREATE TABLE #ErrorLog

(ErrorLog Varchar(1000),

ContinuationRow INT)

-- Inserting the data using SP

INSERT INTO #ErrorLog

EXEC sp_readerrorlog

DELETE FROM #ErrorLog

WHERE

(LEFT(LTRIM(ErrorLog),4)

NOT LIKE DATEPART(YYYY,GETDATE())

AND ContinuationRow = 0)

OR ContinuationRow = 1

OR LEN(ErrorLog) < 25

OR

(CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME)

< CAST(DATEADD(HH,-@HOURS,GETDATE()) AS VARCHAR(23)))

-- see the data

SELECT * FROM #ErrorLog

--Clear the data

DROP TABLE #ErrorLog

Scripts2: To find the Error log in SQL Server 2005.2008 and 2008R2

DECLARE @HOURS INT

SET @HOURS = 24 -- it is the time of error log

-- creating the temp table to store the error log

CREATE TABLE #ErrorLog1

(LogDate DateTime, ProcessInfo Varchar(50),

[Text] Varchar(4000))

-- Inserting the data using SP

INSERT INTO #ErrorLog1

EXEC sp_readerrorlog

DELETE FROM #ErrorLog

WHERE LogDate < CAST(DATEADD(HH,-@HOURS,

GETDATE()) AS VARCHAR(23))

-- see the data

SELECT * FROM #ErrorLog

--Clear the data

DROP TABLE #ErrorLog

 Share if you have any other option to get ERROR Log.