Blog Post

Load SQL error log to a table

Advertisements

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

 

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating