MSSQLWIKI

Karthick P.K on SQL Server

«
»

Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Posted by Karthick P.K on February 13, 2011

Parameters which would be useful when you run xp_readerrorlog or sp_readerrorlog

XP_READERRORLOG

 

Syntax:

xp_readerrorlog -> Reads the current SQL Server errorlog

xp_readerrorlog 3 -> Reads SQL Server errorlog “errorlog.3”

xp_readerrorlog -1, 2 -> Reads the current SQL Agent errorlog

xp_readerrorlog 3, 2 -> Reads the SQL Agent errorlog No 3 archive

xp_readerrorlog -1, 2, “ab” -> Reads the current SQL Agent errorlog but returns only those lines that contain the string “ab”

xp_readerrorlog 1, 2, “ab”, “cd” -> Reads the SQLAgent errorlog No 1 archive but returns only those lines that contain the strings
“ab” AND “cd” lines that contain the strings “ab” AND “cd”

xp_readerrorlog [-1 – n], 3, database_id, fulltext_catalog_id -> Reads full
text crawl log

Note:The second INT param is LogType. 1 = SQLServer logs, 2 = SQLAgent logs, 3 =
full text logs(SQLServer log is the default)
Additionally, the last 3 parameters (parameters 5, 6, 7) are used for Date/Time
filtering and ordering

xp_readerrorlog -1, 1, NULL, NULL, ‘2004-09-01’, ‘2004-09-02’, [‘asc’ | ‘desc’]
-> Reads the current SQL Server error log entries starting from midnight of 2004-09-01 until midnight of 2004-09-02.
Time can be included too. The date-time can be in any format recognizable by SQL server.
The last parameter specifies the datetime order that the entries are returned in. ‘asc’ or no
parameter is used for the normal, ascending, order. ‘desc’ is used to return entries in reverse order,
newest entries first.

Note:Also, to increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\
By default, this key is absent. Modify the value to the number of logs that you want to maintain.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

This entry was posted on February 13, 2011 at 6:39 PM and is filed under SQL Query, SQL Server Tools. Tagged: , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)”

  1. maniveer said

    January 15, 2012 at 7:00 AM

    Karthi,

    I did below :
    To increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\
    By default, this key is absent. Modify the value to the number of logs that you want to maintain.

    I gave the value as 10, but I able to see 14 logs, and it still creates more if I continue the SQL Restart..Do we have an option to restrict the numbers strictly?

    Thanks..
    Mani

    Reply

  2. How to configure the size of SQLServer error log « SQLServerScribbles said

    February 1, 2013 at 1:17 PM

    […] To learn more about sp_readerrorlog and its parameters read Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG) […]

    Reply

  3. Notas sobre el xp_readerrorlog « El Lupanar de Dieguito said

    February 14, 2013 at 3:27 PM

    […] Basado en las notas de sqltechie.com y mssqlwiki.com […]

    Reply

  4. Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said

    January 11, 2014 at 12:59 PM

    […] Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG) […]

    Reply

Leave a comment

«
»