Event Logs

An additional aspect of monitoring that is often disregarded by some administrators is monitoring the various log files available. SQL Server logs certain system events and user-defined events to the SQL Server error log and the Microsoft Windows application log.

Administrators can use information in the SQL Server error log to troubleshoot problems related to SQL Server. In fact, browsing the SQL Server logs for irregular entries is an essential administration task; preferably, it should be carried out on a daily basis to help administrators spot current or potential problem areas. An application-aware solution, such as Microsoft’s System Center Operations Manager (SCOM), helps to automate the process of monitoring SQL (and Lync Server) logs.

SQL Server error log files are simple text files stored on disk, but it is good practice to examine them by using SQL Server Management Studio or by executing the xp_readerrorlog extended stored procedure to prevent SQL operations from being blocked by opening one of the files in a text editor.

A new error log file is created each time an instance of SQL Server is started; however, 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.

The Windows application log describes events that occur on the Windows operating system, as well as other events related to SQL Server and SQL Server Agent. Administrators can use the Windows Event Viewer to view the Windows application log and to filter the information. These event logs are another place that administrators look for information about issues that take place with SQL Server.

In the past, administrators had to view the SQL Server and Windows event logs independently. However, the SQL Server Management Studio Log File viewer makes it possible for administrators to combine both sets of logs into a united view.

Using the SQL Server Log File Viewer

The following steps show how to view the log files using SQL Server Management Studio:

1. Click Start, All Programs, Microsoft SQL Server 2008 R2, and SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. In Object Explorer, expand Management.

4. Right-click SQL Server Logs, click View, and then select either SQL Server Log or SQL Server and Windows Log.

5. Double-click a log file, such as the one shown in Figure 11.16.

Figure 11.16 Select a Log File

In production environments, log files can get quite large and take a long time to open. To avoid huge log files, cycle them on a regular basis. Restarting the SQL Server service is not good practice. Alternatively, the log file can be automatically cycled using the sp_cycle_errorlog system stored procedure. The more writes to the error log, the more often it should be cycled.

To automate the log-cycling process, administrators can use the SQL Server Agent to create a new agent job with a single T-SQL task to execute the stored procedure, or they can include it in a regular daily or weekly maintenance plan. Maintenance plans are covered in-depth earlier in this chapter in “Managing and Maintaining SQL 2008 R2.”

Number of Log Files to Maintain

To keep as much historical information as possible, administrators should configure the number of log files to be retained; this number depends on the amount of disk space available and the amount of activity on the server.

The following steps show how to configure the number of log files to be retained:

1. Click Start, All Programs, Microsoft SQL Server 2008 R2, and SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. In Object Explorer, expand Management.

4. Right-click SQL Server Logs and click Configure.

5. As shown in Figure 11.17, select the check box to limit the number of error logs created before they are recycled. SQL Server retains backups of the previous six logs unless you check this option and specify a different maximum number of error log files.

Figure 11.17 Limit the Number of Error Logs Created before They Are Recycled

6. Specify a different maximum number of error log files, and click OK.