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
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
Papy, from the link you pasted, they only described 4 parameters, not the rest of them.
I forgot the link :
http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005
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
Indeed! Thanks Sankar! I have updated my blog post.