SQL Server errorlog

SQL Server uses Errorlog to record the information in SQL Server startup and operation, for information reference: SQL Server errorlog explained. Generally speaking, errorlog refers to SQL Server Error Log, in fact, there is another type of SQL Server, SQL Server Agent errorlog, which is used to log the Agent's running information.

By default, SQL Server saves 7 errorlog files, named: ERRORLOG,ERRORLOG.N (n=1,2,3,4,5,6). The Errorlog file contains the latest information, and the Errorlog.6 file contains the oldest information. Each time you restart SQL Server, these log files are looped as follows: Delete the errorlog.6 file, Rename errorlog.5 to Errorlog.6, and so on until you rename errorlog to Errorlog.1, and finally create a new Errorlog file that stores information about the SQL Server run process.

The file name for SQL Server Agent errorlog is: SQLAGENT.N (n=1,2,3,4,5,,,,).

The storage path for the errorlog is in:C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\log

1, use SSMs to view

2, use the TSQL script to view

2.1 View Errorlog's information

Use Sys.xp_enumerrorlogs to view the creation date and size of errorlog files, whose creation date is the date the first record was inserted.

exec Sys.xp_enumerrorlogs

Switch errorlog to view information for errorlog files

DBCC errorlog

2.2, read the errorlog information

SQL Server provides stored procedures Sys.xp_readerrorlog and Sys.sp_readerrorlog for viewing the error log for Errorlog records.

Sys.xp_readerrorlog has 7 parameters

1. The archive number (0~99), whose value is the value of the archive# field returned by Sys.xp_enumerrorlogs, 0 means that the errorlog,1 represents Errorlog.1.

2. Log type (1 is SQL Server log, 2 is SQL Server Agent log)

3. Query the included string

4. The string that the query contains, and the logical relationship between parameter 4 and parameter 5 is and (with relation)

5. Logdate Start time

6. Logdate End Time

7. Sorting results, sorted by Logdate (Desc, ASC)

The sys.sp_readerrorlog has four parameters, and the Sys.xp_readerrorlog is the same as the first four parameters, Sys.sp_readerrorlog internal use Sys.xp_readerrorlog to achieve.

To see the error log for login failures, you can see that parameter 4 and parameter 5 are filter text fields.

exec 0,1,'login','failed'

3,errorlog's Rollover

If the current errorlog file is large, the process of loading and viewing is very slow, and you can run sys.sp_cycle_errorlog or DBCC ERRORLOG, or EXEC (' DBCC errorlog< /c10>'), manually force the Errorlog file iteration to avoid a single errorlog of size too large. Each time SQL Server is started, Errorlog automatically iterates. Because errorlog rollover deletes the oldest errorlog.6 file, if you need to save the errorlog, you need to sys.sp_cycle_errorlog before performing errorlog or DBCC errorlog.6 The files are copied to other storage media.

Recommended reading:

SQL Server error log filtering (errorlog)

SQL Server error log shrinking (errorlog)

Appendix

Talking about SQL Server and SQL Server Agent error logs. Here is few interesting things that we can does from Query Analyzer in order to read and analyze the SQL Server and Agent E Rror logs.

1, sys. Xp_readerrorlog

Syntax:xp_readerrorlog a,b,c,d

A--default is 0. It accepts only integers. 0 mean the current error log, 1 means 1st archive and so on.

Default is 1, B. Accepts value equals 1 for SQL Server error log and 2 for SQL Agent error log.

C-accepts varchar chanracter upto 255 characters. Default is Null.

D-accepts varchar chanracter upto 255 characters. Default is Null.

1) to read the current SQL Server error log i.e. errorlog file

00,  

2) to read SQL Server error log archive 1 i.e. errorlog.1 file

11,  

3) to read the current SQL Server Agent error log i.e. SQLAGENT. Out file

0,

4) to read SQL Server error log archive 1 i.e. sqlagent.1 file

1,

5) to search for any specific text on current SQL Server error log

'Failed  

6) to search for entries in current SQL Server error log which contain both ' Failed ' and ' Login ' in a row.

'Failed '.' Login'    

2,sys. Xp_enumerrorlogs

Syntax:xp_enumerrorlogs A

A--default is 1. Accepts value equals 1 for SQL Server error log and 2 for SQL Agent error log.

1) List all the avilable SQL Server error log archives, there last modified date and size.

1

2) List all the avilable SQL Server Agent error log archives, there last modified date and size.

2

3,recycle errorlog

To Recycle current SQL Server Error Log

EXEC sp_cycle_errorlog

To Recycle current SQL Server Agent Error Log

EXEC Sp_cycle_agent_errorlog

Reference doc:

SQL server–read Error Log Data using Sp_readerrorlog–system Stored Procedure

Reading the SQL Server log files using TSQL

Searching through the SQL Server error logs

SQL Server errorlog