Home > T-sql > Query Complete SQL error log in one GO !!

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


CREATE TABLE #read_error_log


logdate DATETIME,

processinfo VARCHAR(200),

errorlogtext VARCHAR(max)


declare @int int= 0

while (@int <= 30)


INSERT INTO #read_error_log

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

set @int= @int+ 1



FROM  #read_error_log


–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 ::




Enjoy 🙂



  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: