xp_readerrorlog

Question

xp_readerrorlog

DBA_123

Old Hand

Points: 347
More actions
July 2, 2013 at 9:50 am

#276917

Hi, I'm trying to search a logfile with xp_readerrorlog for certain text but i would like to exclude databases starting with letter B for example. Does anyone know if this i possible with xp_readerrorlog?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

xpreaderrorlog doesn't have a column dedicated to the database name, so no, there's no way to filter based on a LIKE statement directly.

there's a pretty good recap of the parameters and usage here:

http://blog.sqltechie.com/2011/03/xpreaderrorlog-parameter-detail.html

i would simply stick all the results into a temp table, and then filter the results from there;

something like this looks right to me and my preliminary test:

[#RESULTS]

drop table[#RESULTS]

CREATE TABLE [dbo].[#RESULTS] (

[RESULTSID] INT IDENTITY(1,1) NOT NULL,

[LOGDATE] DATETIME NULL,

[PROCESSINFO] VARCHAR(128) NULL,

[XPTEXT] VARCHAR(max) NULL)

INSERT INTO #RESULTS([LOGDATE] ,[PROCESSINFO],[XPTEXT])

EXEC Xp_readerrorlog 0

SELECT DISTINCT #RESULTS.*

--dbz.*

FROM #RESULTS

left outer join(SELECT name from sys.databases where name like 'D%') dbz

ON CHARINDEX(dbz.name,#RESULTS.XPTEXT) = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

Hi, I'm trying to search a logfile with xp_readerrorlog for certain text but i would like to exclude databases starting with letter B for example. Does anyone know if this i possible with xp_readerrorlog?

Are you trying to read a database transaction log, or the SQL Error Log? xp_readerrorlog is meant for reading the SQL Error Log.

Joie Andrew
"Since 1982"

sandos SSC Veteran Points: 248 More actions · Answer 3

You can execute the below specifying the search string.

EXEC master.dbo.xp_readerrorlog 0, 1, N 'searchstring', NULL, NULL, NULL, N'asc'