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