Reading error log with xp_readerrorlog

Most of us are used with reading error logs through GUI , but when we are really in looking for something in error logs TSQL may seems more use full , so lets look how to analyse log file using xp_readerrorlog

EXEC xp_readerrorlog a,b,c,d,e,f,g

a- Value of log file you want to read ,0 for current, 1 for Archived 1,2 for Archived 2 etc
b-We can chose between error log and agent log here
      1- for error log [default]
      2- for agent log
c-search string which you want to look in log
d-we can give one more string , this can refine the output we got by giving the search string C
e- Here we can from which time's log we want to search
f- Here we can give the to which time's log we want to search
g- Here we can specify whether we want output in ascending or descending order by specifying 'ASC'and 'DESC'

Lets have a look at few examples

Here this will query in Archive #1 error log and will search for logs with word "log" and which are logged after 2015-10-13 and will list the output in ascending order

In the above example we are querying in Archive#2 Agent log , it will search for logs containing both the search strings "error" and "network" which are logged between 2015-09-20 and 2015-09-24 and will list the output in descending order 

Cycling SQL Server error log

It is a good practice to cycle sql server error log as also it is easy to handle error logs of small size , By default sql server will only cycle error logs once its starts , so for easy managing of error logs its better to cycle error logs , in most of the productions system we create job to cycle error logs  that runs daily /weekly/ monthly depends on the volume of contents that will be written to the error logs. Once the error log cycle the current error log is renamed as errorlog.1 ,

We can use stored procedure   ' sp_cycle_errorlog' to cycle error log , members of sysadmin fixed roles can trigger this procedure , so by using the below T-sql we can cycle the error log 

exec sp_cycle_errorlog

go

By creating a job with above query we can automate this task 

By default sql server retains current and 5 recent error logs , this can be changed in SSMS by expanding Management\Sql Server log [right clich ] -> configure select Limit The Number Of Error Logs Before They Are Cycled, and select the maximum number of error logs, as shown in  Figure , Minimum limit is 6 and maximum is 99