Page 179 - DCAP508_DATABASE_ADMINISTRATION

Unit 12: Tools and Techniques for Monitoring Performance

Self Assessment Notes

State true or false:
8. System Monitor makes it possible to obtain up-to-the-second SQL Server activity and
performance statistics.

9. The current activity window in SQL Server Enterprise Manager cannot be used to perform
ad hoc monitoring of an instance of SQL Server.
10. SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.

11. When the process is waiting, the wait time is zero.

12.8 Monitoring the Error Logs

Microsoft® SQL Server™ logs events (although only certain system events and user-defined
events) to the SQL Server error log and the Microsoft Windows® application log. Use the
information in the error log to troubleshoot problems related to SQL Server.
The Windows application logs provide an overall picture of events that occur on the Windows
NT® 4.0 and Windows 2000 systems, as well as events in SQL Server and SQL Server Agent. Use
Event Viewer to view the Windows application log and to filter the information. For example,
you can filter events, such as information, warning, error, success audit, and failure audit.
Both logs automatically timestamp all recorded events.

12.8.1 Comparing Error and Application Log Output

You can use both the SQL Server error log and the Windows application log to identify the cause
of problems. For example, while monitoring the SQL Server error log, you may detect a certain
set of messages for which you do not know the cause. By comparing the dates and times for
events between these logs, you can narrow the list of probable causes.

12.8.2 Viewing the SQL Server Error Log: SQL Server 2000

View the Microsoft® SQL Server™ error log to ensure that processes have completed successfully
(for example, backup and restore operations, batch commands, or other scripts and processes).
This can be helpful to detect any current or potential problem areas, including automatic recovery
messages (particularly if an instance of SQL Server has been stopped and restarted), kernel
messages, and so on.
View the SQL Server error log by using SQL Server Enterprise Manager or any text editor. By
default, the error log is located at Program Files\Microsoft SQL Server\Mssql\Log\Errorlog.
A new error log is created each time an instance of SQL Server is started, although the
sp_cycle_errorlog system stored procedure can be used to cycle the error log files without
having to restart the instance of SQL Server. Typically, SQL Server retains backups of the previous
six logs and gives the most recent log backup the extension .1, the second most recent the
extension .2, and so on. The current error log has no extension.

12.8.3 Viewing the Windows Application Log

When Microsoft® SQL Server™ is configured to use the Microsoft Windows® application log,
each SQL Server session writes new events to that log. Unlike the SQL Server error log, a new
application log is not created each time you start an instance of SQL Server.

LOVELY PROFESSIONAL UNIVERSITY 173