Query Complete SQL error log in one GO !!

Hi All,

this post coming after a long time.

 

if you need to search a particular word or error code in the SQL error long,then you would have to do it one by one .

i.e .. you will proceed like below (let say for example we are looking for  connection timeout errors in Always ON setup, this could be any error or a

particular  word you are interested in looking for)

to query the current SQL error log

EXECmaster.dbo.Xp_readerrorlog 0,1,N‘A connection timeout’

0 –> current logs  | 1 –> SQL error log (also if you specify nothing in this place .. it is taken as SQL error log by default)

third parameter is something you are searching for in the error log ..

for the previous error log (ie errorlog.1) we have to use EXECmaster.dbo.Xp_readerrorlog 1,1,N‘A connection timeout’

so this becomes tedious over time .. let put this in a loop as below  …

assuming you have total of 30 previous long retained ..

use tempdb

go

CREATE TABLE #read_error_log

(

logdate DATETIME,

processinfo VARCHAR(200),

errorlogtext VARCHAR(max)

)

declare @int int= 0

while (@int <= 30)

begin

INSERT INTO #read_error_log

EXEC master.dbo.Xp_readerrorlog @int,1,N’A connection timeout’

set @int= @int+ 1

End

SELECT *

FROM  #read_error_log

ORDER BY logdateDESC

–TRUNCATE TABLE #read_error_log

–DROP TABLE #read_error_log

 

 

The above query will give you results much faster then query individual error logs ..

Refer below Links for more details on reading error logs ::

http://mssqlwiki.com/tag/sp_readerrorlog-parameters/

http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

http://strictlysql.blogspot.in/2013/05/xpreaderrorlog-in-sql-server-2012.html

Enjoy 🙂