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

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

  1. maniveer said

    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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,296 other followers

%d bloggers like this: