SQL Server Agent error log know how much

An overview

By default, the SQL Server Agent creates error logs to record warnings and errors. The following warnings and errors are displayed in the log:

A warning message that provides information about a potential problem, such as "a job is deleted when it is executed."

Error messages, which typically require system administrator intervention, such as "Unable to start a mail session." You can send an error message to a specific user or computer through net send.

By default, execution trace messages do not write to SQL Server Agent log errors because they fill the log. If the error log is full, the ability to select and analyze more serious errors is reduced. Because the log increases the processing load on the server, it is important to consider carefully whether it is worth capturing the execution trace message to the error log. Generally, it is best to capture all messages only when you are debugging a particular problem.

Storage directory and number of two error logs

SQL Server can maintain a maximum of one current error log record and 9 SQL Server Agent error log history. Each archive log has an extension that indicates the relative time of the log. For example, the extension. 1 represents the most recent archive error log, while the extension. 9 represents the oldest archived error log.

After the SQL Server Agent has stopped, you can modify the location of the SQL Server Agent error log. If the error log is empty, the log cannot be opened. You can iterate through the SQL Server Agent log at any time without stopping the SQL Server Agent.

Three basic management tasks

View SQL Server Agent error log: Enterprise Manager; Read files through OpenRowset; xp_cmdshell the type command to execute DOS output; xp_cmdshell under bcp into a table in the database system; EXEC xp_cmdshell ' Type ' C:Program filesmicrosoft SQL servermssql.1mssqllogsqlagent.out '

Renames the SQL Server Agent error log and takes effect after restarting the SQL Agent: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file =n ' C:Program filesmicrosoft SQL Servermssql.1mssqllogsqlagent.out '

Sends a SQL SERVER agent error message that uses net send to send messages, depending on the Windows Messenges Service: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @error_recipient =n ' Jinsen '

Writes the execution trace message to the SQL Server Agent error log: Configures the SQL Agent to record the trace content;

Configure the agent error log information content type (Error 1, warning 2, information (including error tracking) 4), types can be superimposed: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level ={1|2|4|3|5|7|6}

Configure the encoding type of the information content of the agent error log: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @oem_errorlog ={0|1}

Space to recycle error log: Use Enterprise Manager; EXEC Msdb.dbo.sp_cycle_agent_errorlog

To configure the size (number of lines) of the agent error log file record: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 1000,

Configure the agent error log file to record the maximum record size (number of rows) per job: Enterprise Manager; EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows_per_job =100

Four FAQ

Q: Why is the contents of the SQL Agent error log file always empty and cannot be logged with error messages?

A: Reconfiguring the SQL Agent error log file is encoded in a oem--that is configured for Unicode encoding to resolve the problem

Q: Why is there an exception to the log viewer when using Enterprise Manager to view the SQL Agent error log, "Unicode file expected"?

A: Reconfiguring the SQL Agent error log file is encoded in a oem--that is configured for Unicode encoding to resolve the problem

Q: Why is Enterprise Manager, SMS Unable to recycle SQL2000 SQL Agent error log files?

a:sqlserver2000 SQL Agent error log file does not support recycling!