Event Logs

An additional aspect of monitoring 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. 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 any current or potential problem areas. An application-aware solution such as Microsoft’s System Center Operations Manager (SCOM) can help to automate the process of monitoring SQL (and SharePoint) 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 any SQL operations from being blocked by opening one of these 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 and 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 should be another place that administrators go to look for information about any 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, 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 any log file, such as the one shown in Figure 9.2.

   Figure 9.2. Using the SQL Server Log File Viewer.

   

Log File Cycling

One thing administrators should keep in mind is that in production environments, log files can get quite large and take a long time to open. To avoid huge log files, it is a good idea to 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 utilize 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 will be covered in depth later in this chapter.

Number of Log Files to Maintain

To keep as much historical information as possible, it is recommended that administrators 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, 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 9.3, check the 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 9.3. Configuring the number of log files to be retained.

   

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