Event logs provide historical information that can help you track down problems with SQL Server. SQL Server writes events to the SQL Server event logs, the SQL Server Agent event logs, and the Windows application log. You can use all three logs to track messages related to SQL Server. However, there are some things you should know about these logs:
Only the application log provides additional information on all applications running on the server, and only the application log provides features for filtering events based on type. For example, you can filter events so that only error and warning messages are displayed.
If you start the MSSQLServer or MSSQL$instancename service from the command prompt, events are logged to the SQL Server event log and to standard output. No events are recorded in the Windows application log.
Windows has additional logs that can be helpful when tracking issues. If you are tracking security issues, start with the SQL Server event logs and also examine the Windows security log. If you are having trouble finding the source of a problem that is preventing proper operation of SQL Server, start with the SQL Server logs and also examine the Windows application and system logs.
SQL Server error messages can be cryptic and difficult to read if you do not understand the formatting. Error messages logged by SQL Server can have:
An error number that uniquely identifies the error message. System error numbers have one to five digits. System errors are numbered from 1 to 50,000. User-defined errors start at 50,001.
A severity level that indicates how critical the message is. Severity levels range from 1 to 25. Messages with a severity level of 0 to 10 are informational messages. Severity levels from 11 to 16 are generated by users and users can correct them. Severity levels from 17 to 25 indicate software or hardware errors that you should examine.
An error state number that indicates the source of the error. Error state numbers have one to three digits and a maximum value of 127. Normally, error state numbers indicate the line number in the SQL Server code that generated the message.
A message that provides a brief description of the error. Read the message to get more information about the error, which will help you in troubleshooting problems.
You may see ODBC (open database connectivity) and OLE (object linking and embedding) return errors from SQL Server that contain similar information as well. The sysmessages table in the master database contains a list of error messages and descriptions that can be returned by SQL Server. To see all error messages that can be returned by SQL Server, you can execute the following T-SQL statement:
USE master GO SELECT * FROM sysmessages
The application log contains entries for all database server instances running on the computer as well as entries for other business applications. You access the application log by completing the following steps:
Click Start | Programs or All Programs | Administrative Tools, and then choose Event Viewer. This starts Event Viewer.
Event Viewer displays logs for the local computer by default. If you want to view logs on a remote computer, right-click the Event Viewer entry in the console tree (left pane), and then select Connect To Another Computer to display in the Select Computer dialog box. In the dialog box, enter the name of the computer you want to access, and then click OK.
In the console tree (left pane), click Application Log. You should see an application log similar to the one shown in Figure 13-3. Use the information in the Source column to determine which service or database server instance logged a particular event.
The entries in the main window of Event Viewer provide a quick overview of when, where, and how an event occurred. To obtain detailed information about an event, double-click its entry. A summary icon that tells you the event type precedes the date and time of the event. Event types include:
Informational. An informational event that is generally related to a successful action
Success audit. An event related to the successful execution of an action
Failure audit. An event related to the failed execution of an action
Warning. A noncritical error that provides a warning. Details for warnings are often useful in preventing future system problems.
Error. An error, such as the failure of a service to start
In addition to the date, time, and event type indicator, the summary and detailed event entries provide the following information:
Source. The application, service, or component that logged the event
Category. The category of the event, which is sometimes used to further describe the related action
Event_ID. An identifier for the specific event
User. The user account that was logged on when the event occurred
Computer. The computer name on which the event occurred
Description. A text description of the event, provided in detailed entries
Data. Any data or error code output by the event, provided in detailed entries
Warnings and errors are the two main types of events that you want to examine closely. Whenever one of these types of events occur and you are unsure of the cause, double-click the entry to view the detailed event description. If you want to see only warnings and errors, you can filter the log by completing the following steps:
From the View menu, choose the Filter option. This opens the dialog box shown in Figure 13-4.
Clear the following check boxes: Information, Success Audit, and Failure Audit.
Select the Warning and Error check boxes, if they are not already selected.
Click OK. You should now see a list of warning and error messages only. Remember that these messages are for all applications running on the server and not just for SQL Server.
The SQL Server logs record information, warnings, errors, and auditing messages pertaining to SQL Server activity. New logs are created when you start the SQL Server service or when you run the sp_cycle_errorlog stored procedure. When a new log is created, the current log is cycled to the archive. SQL Server maintains up to five archived logs (by default).
You can view the SQL Server event logs in SQL Server Management Studio or through a text editor. In SQL Server Management Studio, you access the event logs by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server of your choice, and then work your way down to the Management folder.
Expand the Management folder, and then double-click the SQL Server Logs entry. The current log is shown with the label Current. Archived logs are shown with descriptive labels such as Archive #1.
Double-click the log you want to view to open it in the Log File Viewer.
With the Log File Viewer open, you can add other logs to the log file summary by selecting their check boxes, as shown in Figure 13-5.
To access the event logs in a text editor, complete the following steps:
Start a text editor, such as WordPad, and then use its Open dialog box to access the SQL Server. Log folder, normally located in MSSQL.1mssqlLog or MSSQL.1mssql$instancenameLog.
Open the log you want to examine. The current log file is named ERRORLOG with no file extension. The most recent log backup has the extension .1, the second most recent has the extension .2, and so on.
To change the number of logs that SQL Server maintains, right-click the SQL Server Logs entry in Object Explorer view and select Configure. In the Configure SQL Server Error Logs dialog box, select Limit The Number Of Error Log Files..., and then set the maximum number of error log files to retain using the Maximum Number Of Error Log Files combo box. The default number of log files maintained is six: one current log and five archive logs. You can change the number of logs maintained to any value between 6 and 99.
The SQL Server Agent logs record information, warnings, and errors pertaining to SQL Server Agent activity. New logs are created only when you start the SQL Server Agent service. When a new log is created, the current log is cycled to the archive. SQL Server maintains up to five archived agent logs (by default).
In SQL Server Management Studio, you access the current SQL Server Agent log by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server of your choice, and then work your way down to the SQL Server Agent node.
Expand the SQL Server Agent node, and then double-click the SQL Server Agent Error Logs entry. The current log is shown with the label Current. Archived logs are labeled Archive #1 and so on.
Double-click the log you want to view to open it in the Log File Viewer.
With the Log File Viewer open, you can add other logs to the log file summary by selecting their check boxes.
To access archived SQL Server Agent event logs in a text editor, complete the following steps:
Start the text editor, and then use its Open dialog box to access the SQL Server Log folder, which is normally located in mssqlLog or mssql$instancenameLog.
Open the log you want to examine. The current log file is named SQLAGENT.OUT. The most recent log backup has the extension .1, the second most recent has the extension .2, and so on.
You can manage the SQL Server Agent logs in several ways. You can force the SQL Server Agent to recycle the current log by right-clicking the SQL Server Agent Error Logs node in Object Explorer view and then selecting Recycle and clicking OK. When you do this, SQL Server closes out the current agent log, moves it to an archive log, and starts a new agent log. You can control the level of logging and set the log file location as well. To do this, complete the following steps:
Right-click the SQL Server Agent Error Logs node in Object Explorer view, and then select Configure.
Use the Error Log File box to set the folder path and file name of the agent log. The default path is %ProgramFiles% Microsoft SQL ServerMSSQL.1 MSSQLLOGSQLAGENT.OUT. New archive files will also be created in the folder specified as part of the path.
Use the Agent Log Level check boxes to control the level of logging for the SQL Server Agent. By default, only error and warning messages are logged. If you want to view informational messages in the logs, select the Informational check box as well.
Click OK.