Sp_readerrorlog and xp_readerrorlog there are two stored procedures which helps to read SQL Server error log information using T-SQL Commands.
Sys.sp_readerrorlog syntax has 4 parameters Log File Number, LogFileType, SearchString1, SearchString2
Log File Number -- Indicates which file you want to read 0 for current and 1 for Archive#1 and so on
LogFile Type -- 1 is for SQL Server Error log and 2 is for SQL Agent log
SearchString1 -- Displays lines which has matched string value (for example if you want to search
for Failed keyword you need to specify “Failed” as parameter)
SearchString2 -- Further filter on search string1.
sys.sp_readerrorlog 2,2, 'Failed' -- Displays the lines which contains Failed Keyword from Archive#2 file
sys.sp_readerrorlog 2,2, 'Failed', ‘Logins’ -- Displays the lines which contains Failed Keyword with only Login failures (it is a further search on searchstring1)
In addition to sp_readerrorlog there is another extended stored procedure xp_readerrorlog which took 7 parameters
Xp_readerrorlog Lognumber, Logtype, SearchString1, SearchString2, StartTime, EndTime, SortOrder
First 4 parameters are common for sp_Readerrorlog and xp_readerrorlog sps,
StartTime -- Starts reading logs from the specified start date onwards
EndTime -- Reads logs till specified end date
SortOrder -- ASC or DESC
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20131105', NULL,’DESC’ – This command displays all lines from current server log file start searching from 05-Nov-2013 onwards to till date becoz the second parameter is null and displays results in DESC order
Xp_enumerrorlogs -- Displays list of errorlog files for the current instance
Xp_enumerrorlogs (or) xp_enumerrorlogs 1
xp_enumerrorlogs 2 -- Displays list of agent error log files for the current instance
exec sp_cycle_errorlog -- Recycles SQL Server error log
exec sp_cycle_agent_errorlog -- Recycles SQL Server Agent error log.
xp_readerrorlog 0,1, "Logging SQL Server messages in file" -- Simple command to display path of SQL Server log file.
No comments: