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
-- 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



I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published.

WC Captcha 30 + = 31