xp_readerrorlog V/S sp_readerrorlog parameters in SQL Server

Difference between xp_readerrorlog & sp_readerrorlog

sp_readerrorlog is a stored procedure in the master database that checks that the user is part of the securityadmin group, then calls xp_readerrorlog, passing parameters if available.  Any differences between the two procedures does not affect the output, which means the information returned is the same.

The parameters syntax has been changed in SQL Server 2012 but not documented yet.

sp_readerrorlog 0,1,’node’

xp_readerrorlog 0,1, ‘node’ -- Not supported

sp_readerrorlog 0,1, "node"

xp_readerrorlog 0,1,"node"

Parameters defined

* First Parameter (type int) :- is the number of the log file you want to read, default is "0" for current log.

* Second Parameter (type int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1

* Third Parameter varchar (255), is a search string for the log entry, with a default value of NULL

* Forth Parameter varchar (255), is another search string for the log entry, with a default value of NULL

expanding the number of SQL Server error logs

To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log.

Finally, to address not loosing the historical SQL Server error log, the number of logs should be expanded beyond the default of 7.  The maximum number of logs is 99 for the SQL Server error log.  When it comes to expanding the number of SQL Server error logs, follow these steps:

• Open Management Studio
• Navigate to root | Management folder | SQL Server Logs folder
• Right click on the SQL Server Logs folder and select the 'Configure' option
• Select the 'Limit the number of error log files before they are recycled' check box
• Reference the screen shot below as a point of reference
• Enter the desired number of error logs in the 'Maximum number of error log files'
• Reference the screen shot below as a point of reference   
• Press the 'OK' button to save the configuration

Hope you find this interesting.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)