Sunday, January 20, 2013

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.

Like and Share to SQL Integrity Blog

No comments:

Post a Comment

Thank You !!!!