Friday, November 8, 2013

Reading SQL Server Error Log Files using TSQL

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: