• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

Query your SQL Server ERRORLOGS

April 2, 2017 by Kevin3NF Leave a Comment

Quick post in response to a number of Twitter and Forum questions I’ve seen lately.

Downloadable from the Microsoft code Gallery (with ranking options!)

“How can I search my SQL ERRORLOGS with native tools?”

Create Table #Errorlog 
	(Logdate datetime, 
	 ProcessInfo varchar(50), 
	 LogText varchar(5000))

--Dump all the things into the table
insert into #Errorlog
EXEC sys.xp_readerrorlog 
	0			-- Current ERRORLOG
	,1			-- SQL ERRORLOG (not Agent)

--Query just like you would anything else:
Select * 
from #Errorlog 
Where 1=1
	--and LogText like '(c) Microsoft Corporation%'
	and (LogText like '%Error%'	or LogText like '%Fail%'--or LogText like '%SPN%'
	)
	And Logdate > getdate() -1
	And LogText Not Like '%CheckDB%'
	And LogText not like '%35262%'
	And LogText not like '%35250%'

--Clean up your mess, you weren't raised in a barn!
Drop Table #Errorlog

I run this every morning against my Prod Local Server Group in SSMS Registered Servers

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: SQL

About Kevin3NF

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • HealthCheck
  • Index
  • Install
  • IRL
  • Management
  • Migration
  • MySQL
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • Podcast
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • Upgrade
  • vendor code modify
  • video
  • vNext

Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy