Load SQL error log to a table

SQL server error log load to a table

There are cases, we need a SQL server error log load to a table for troubleshooting. Since there will be a more informational message and logon enabled etc.

Here is the script to load and read.

-- create table 

use dba
go
-- drop table [tbl_errorlog_from_Nov_16_2018]

create table [tbl_errorlog_from_Nov_16_2018] ( loaddate datetime, info varchar(20), text varchar(max))

-- load error log from 0 to 6 numbers default
insert into  [tbl_errorlog_from_Nov_16_2018] exec master..sp_readerrorlog 0

-- select errorlog

select   * from  [tbl_errorlog_from_Nov_16_2018] 
--where text  like '%memory%'


-- delete unwanted information
delete from  [tbl_errorlog_from_Nov_16_2018] where text  like 'Login%'
delete from  [tbl_errorlog_from_Nov_16_2018] where text  like '%Error: 18456%'
delete from  [tbl_errorlog_from_Nov_16_2018] where text  like '%backed up%'
delete from  [tbl_errorlog_from_Nov_16_2018] where text  like '%backup%'

delete from  [tbl_errorlog_from_Nov_16_2018] where text  like '%DBCC TRACEOFF 3604%'
delete from  [tbl_errorlog_from_Nov_16_2018] where text  like '%TRACE%'



select  * from  [tbl_errorlog_from_Nov_16_2018] order by 1 desc