Looking for any problems in SQL error log

Question

Looking for any problems in SQL error log

Roust_m

SSCoach

Points: 17410
More actions
December 11, 2006 at 10:04 pm

#62684

Hi,
I am using the below code to find out if there were any problems in the last 24 hours. I am using '%Error:%' as a search criteria to look for errors. What other search strings can be used to find any problems in the log?
Thanks.
CREATE TABLE #Errors (vchMessage varchar(255), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)
INSERT #Errors EXEC xp_readerrorlog
SELECT vchMessage FROM #Errors
WHERE vchMessage LIKE '%Error:%' and vchMessage like Convert(varchar, DatePart(yy, Getdate())) + '%'
and DateDiff(hh,CONVERT(DateTime, LEFT(vchMessage, 22)), GetDate())<=24
ORDER BY ID
DROP TABLE #Errors

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

We search for the strings "MSG ", "ERROR:" and "ERROR." during our check. This procedure is generic for SQL Server (7.0 and 2000) and Sybase, so you may not need them all for SQL Server.

John

MohammedU SSCoach Points: 19644 More actions · Answer 2

Here is what I am using...

select

@@servername

, SUBSTRING(vchMessage,34,8000)

, SUBSTRING(vchMessage,1,22)

from #Errors

where isdate(substring(vchMessage,1,10)) = 1

and

(vchMessage like "%backupmedium%" or

vchMessage like "%failed%" or

vchMessage like "%failure%" or

vchMessage like "%severity: 1[789],%" or

vchMessage like "%severity: 2[012345],%" )

and

(

vchMessage not like "%DBCC CHECKALLOC%0 errors%" or

vchMessage not like "%DBCC CHECKTABLE%0 errors%"

)

MohammedU
Microsoft SQL Server MVP

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

I guess you meant sp_readerrorlog not xp_readerrorlog. However, the ID column always seems to be zero.

Roust_m SSCoach Points: 17410 More actions · Answer 4

No, I meant xp_readerrorlog. The the sp_readerrorlog is just a wrap SP for xp_readerrorlog as you can see from it's code below.

create proc sp_readerrorlog(

@p1 int = 0,

@p2 varchar(255) = NULL,

@p3 varchar(255) = NULL,

@p4 varchar(255) = NULL)

as

begin

IF (not is_srvrolemember(N'securityadmin') = 1)

begin

raiserror(15003,-1,-1, N'securityadmin')

return (1)

end

if (@p1 = 0)

exec master.dbo.xp_readerrorlog

else if (@p2 is NULL)

exec master.dbo.xp_readerrorlog @p1

else

exec master.dbo.xp_readerrorlog @p1,@p2,@p3,@p4

end

GO

MohammedU SSCoach Points: 19644 More actions · Answer 5

You can either one...

sp_readerrorlog or xp_readerrorlog

MohammedU
Microsoft SQL Server MVP