SQL Server Error Log

SQL Server Error Log records information about SQL Server during runtime. It’s created under SQLServerRootFolder\MSSQL\Log folder for each start of SQL Server. Information in the file is varied. Some of them are information only; no action needed to be taken. Some of them are really the information you want to look at. The simplest way accessing it is to use SSMS->Management->SQL Server Logs. Bur when the log is huge, Log File Viewer will retrieve all logs back until hit the end of the log or all your local memory is exhaused. Here is a simple way you can recyle them.

use master
exec sp_cycle_errorlog
--Internally, this procedure calls dbcc errorlog() to recyle error logs

By default, only 6 SQL Server logs are kept. you can change that number by modifying registry entry.

use master
go
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
	N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	N'NumErrorLogs', 
	REG_DWORD,
	8 
go

Use this procedure to check number of SQL Server error log you have. Passing 2 will show you number of Agent log you have.

exec xp_enumerrorlogs 1

Use this procedure to read the current Error log. This command is captured by SQL Profiler. If you only need to look at the current Error log, you don’t have to pass any parameters.

EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'
  • First parameter: Error Log number, start with 0
  • Second parameter: 1: SQL Server Error Log. 2: Agent Error Log.
  • Third and fourth parameter: Filters
  • Fifth and sixth: Start Datetime and End Datetime
  • Seventh: Desc or Asc

5 thoughts on “SQL Server Error Log”

  1. For the parameters, i found this link but it is for SQL Server 2005 and i don’t know if it is always useful
    Anyway, you provided an useful document.Thanks

    Reply
  2. Here are the complete descriptions for all of the parameters.

    EXEC xp_readerrorlog
    0 –number
    , 1 –Error Log vs agent log etc
    , ‘Backup’ –first filter for Text column
    , ‘abc’ –second filter for Text column
    , ‘20111122’–start date
    , ‘20111124’–end date
    , ‘asc’ –Order of data results

    Reply

Leave a Comment

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.