Error logs for SQL Server logs and proxies

This article describes a log that is not a transaction log, but an error log for SQL Server logs and agents that divides the error log into SQL Server, SQL Server Agent, Database Mail, and Windows NT, according to the principal. SQL Server uses logging information generated during startup and operation of the database engine, which is not necessarily an "error" message, and is divided into three levels according to the severity of the message, namely: information (information), Warning (Warning), and Errors (error), The SQL Server log records messages in the chronological order in which they occurred. SQL Server logs are used to log messages generated by the SQL Server engine, and the SQL Server Agent error log is used to record information generated during the agent's run.

One, the file that stores the log

By default, the log file holds the path:C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\log, the system maintains 7 SQL Server log files, the file names are: ERRORLOG,ERRORLOG.N (N=1, 2, 3, 4, 5, 6), where the Errorlog file is the currently active file that contains the latest information, the errorlog.6 file records the earliest generated message. SQL Server logs messages in the latest file errorlog each time. In a production environment, the size of the Errorlog file can be large, so it is necessary to periodically clean up this error log file, which is updated in the form of cyclic update (cycle), the so-called cyclic update process, which means: 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 records the information generated during SQL Server run.

The system maintains 10 SQL Server agent error log files, and the proxy error log file is stored in the following path:C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\log, The filenames are: Sqlagent.out, SQLAGENT.N (N=1, 2, 3, 4, 5, 6, 7, 8, 9), Where Sqlagent.out records the agent's current message, which is updated in the same way as the SQL Server log file.

Users can modify the default storage path for log files by modifying the startup parameters: Modify the startup parameters starting with –e, modify the path following the parameter to a new path, and then store the error log under the specified path, and the update will not take effect until the service is restarted.

The SQL Server log and agent error log files, which use circular updating to control the size of the file, start the process of log looping updates, with 2 triggers: Restart the SQL Server instance and manually perform the cyclic update. In general, the size of the error log is unrestricted, and a single log file is too large, which can cause file opening and viewing to be time-consuming. In practice, it is usually necessary to establish a job timing to execute the stored procedure, so that the size of the log file can be controlled within a reasonable range.

Second, use the log viewer to view messages

Users can use the Log viewer to view the memory of the log, or they can use the TSQL command. The user opens Management view SQL Server Logs, selects "current-xxx" to view the current log file, and double-clicks the Log Viewer to open the log.

Users can also right-click on SQL Server Logs, configure and recycle log files, and in the log viewer, filter and search through filters or searches.

third, use the TSQL script to view the message

TSQL provides multiple system stored procedures for viewing the contents of a log file and recycle the log file.

1. View Errorlog Metadata

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

exec Sys.xp_enumerrorlogs

2, read the errorlog information

SQL Server provides stored procedures Sys.xp_readerrorlog and sys.sp_readerrorlogfor viewing the error log.

The sys.xp_readerrorlog stored procedure has 7 parameters, in order of the parameters, they are:

1. @Archive, the archive number (0~99), whose value is the value of the archive# field returned by Sys.xp_enumerrorlogs, the default value is 0, and 0 for the errorlog,1 represents Errorlog.1.
2. @Logtype, the log type, valid values are 1 and 2, 1 represents the SQL Server log, 2 represents the SQL Server Agent log, and the default value is 1.
3. @SearchText1, the query contains the string, the size is 255, the default value is NULL,
4. @SearchText2, the query contains the string, the size is 255, the default value is NULL, and the logical relationship of parameter 3 and parameter 4 is and (with the relationship), which means that both literals are included.
5. @StartTime, the start time of the message
6. @End ' time, end of message
7. @Order, sort the results, sort the output 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'

Cyclic update of 3,errorlog

Log files are updated using the (Rollover) method, and if the current active Errorlog file is large, the loading and viewing process is slow and can run sys.sp_cycle_errorlog or DBCC Errorlog, manually force errorlog file iterations to avoid a single errorlog file too large.

Toggle errorlog, view information for errorlog files

DBCC errorlog

Iterate over the agent's error log file:

exec Sp_cycle_agent_errorlog

Each time SQL Server is started, Errorlog automatically iterates. Because Errorlog's cyclic update (Rollover) deletes the oldest created errorlog.6 file, if you need to save the errorlog, before you perform sys.sp_cycle_errorlog or DBCC errorlog, You need to copy the errorlog.6 file to another storage medium.

Reference Documentation:

SQL Server error log filtering (errorlog)

SQL Server error log shrinking (errorlog)

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

Error logs for SQL Server logs and proxies