Query SQL Error Log with T-SQL

Question

Hi Team,

I am treying to query SQL error log using T-sql.
There is some issue with T-SQL. I am not getting error logs from last 24 hrs.

CREATE TABLE #ErrorLogInfo                                  
(                                  
 ID INT IDENTITY PRIMARY KEY NOT NULL,  
 LogDate  varchar(100),    
 Processinfo varchar(200),  
Text  varchar(Max)   
)  
  
--DECLARE @A VARCHAR(10), @B VARCHAR(10);  
DECLARE @A VARCHAR(10), @B VARCHAR(10);--,@C VARCHAR(10);  
SELECT @A = CONVERT(VARCHAR(20),GETDATE()-1,112);  
SELECT @B = CONVERT(VARCHAR(20),GETDATE()-1,112);  
SELECT @C = CONVERT(VARCHAR(20),GETDATE()-1,112);  
INSERT INTO #ErrorLogInfo_all  
EXEC SP_READERRORLOG 0,'DESC';  
  
INSERT INTO #ErrorLogInfo (LogDate,Processinfo,Text)  
select DISTINCT CONVERT(VARCHAR(20),GETDATE()-1,111) "LogDate",Processinfo,Text  from #ErrorLogInfo_all;  


  

Answer 1

Hi @VijayKumar-4406,

You could use xp_readerrorlog instead.

Please refer below example:

drop table if exists #ErrorLogInfo  
  
CREATE TABLE #ErrorLogInfo                                  
 (                                  
  ID INT IDENTITY PRIMARY KEY NOT NULL,  
  LogDate  varchar(100),    
  Processinfo varchar(200),  
 Text  varchar(Max)   
 )  
  
 DECLARE @A VARCHAR(20), @B VARCHAR(20)  
 SELECT @A = CONVERT(VARCHAR(20),GETDATE()-1,113);  
 SELECT @B = CONVERT(VARCHAR(20),GETDATE(),113);  
  
 INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 0,1,null,null,@a,@b  
  INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 1,1,null,null,@a,@b  
  INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 2,1,null,null,@a,@b  
  INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 3,1,null,null,@a,@b  
  INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 4,1,null,null,@a,@b  
   INSERT INTO #ErrorLogInfo  
 EXEC master.sys.xp_readerrorlog 5,1,null,null,@a,@b  
  
 select * from #ErrorLogInfo  

You could refer more details from below link:
Read SQL Server error logs using the xp_readerrorlog command

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 2

sp_help sp_readerrorlog tells me that the second parameter is an integer, so the above will fail with a conversion error.

Why do you have logDate as varchar(100) in your table? Why not datetime2(3)?

If you want for the last 24hrs, why do you dabble with formats 111 and 112 that are date only? Why not just dateadd(HOUR, -24, sysdatetime())?

Answer 3

If you want to have the search results sorted in a descending order, you have to pass 7 parameters to the extended stored procedure master.sys.xp_readerrorlog:

-- List all rows from the current error log file
EXEC master.sys.xp_readerrorlog 0;  -- 0 = current, 1 = Archive #1, 2 = Archive #2, etc.
-- Search one string
EXEC master.sys.xp_readerrorlog 0, 1, N'SEARCH_STRING';
-- Search both strings from
EXEC master.sys.xp_readerrorlog 0, 1, N'SEARCH_STRING_1', N'SEARCH_STRING_2';
-- Search results in an ascending order or in a descending order
EXEC master.sys.xp_readerrorlog 0, 1, 'SEARCH_STRING_1', 'SEARCH_STRING_2', 'START_TIME(date type)', 'END_TIME(date type)', 'DESC';