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
MSSQLWIKI 