Monitoring SQL Server 2005
Monitoring SQL Server 2005 is key to ensuring the system stays up and operational with as few unplanned interruptions as possible. When problems do occur, monitoring ensures that problems are quickly identified and corrected. Problems left unattended can quickly grow into major issues if not dealt with.
With the built-in SQL Server 2005 native monitoring tools, you can configure alerts for conditions and examine some of the historical information that has been logged on the server. With an external solution such as Microsoft System Center Operations Manager (OpsMgr) 2007, monitoring can be taken to the next level by leveraging built-in knowledge for hundreds of common problems, performance can be tracked and reported, and uptime reports can be easily generated.
Monitoring SQL Server with Native Tools
SQL Server 2005 provides several built-in tools that assist in your ongoing monitoring efforts. Database administrators commonly use these tools to verify the different SQL Server components are running correctly and to troubleshoot problems as they are encountered. SQL Server 2005 also introduces a significant improvement in the way notifications are sent so you can be alerted when specific events occur. The following sections provide a look into several monitoring tools and demonstrate how to set up each tool.
Monitoring Job Activity
The Job Activity Monitor allows the monitoring of all agent jobs for a specific SQL Server instance through the SQL Server
Management Studio (SSMS). To view all jobs with the Job Activity Monitor, follow these steps:
1. From the test server (SQL01), choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
2. Select Database Engine from the Server Type drop-down; then enter the server and instance name (SQL01\INSTANCE01).
3. Select Windows Authentication from the Authentication drop-down menu and then click the Connect button.
4. A connection to the Database Engine is made. If the Object Explorer pane is not visible, press the F8 button.
5. Expand the SQL Server Agent container.
6. Right-click Job Activity Monitor.
7. Select View Job Activity.
Within the Job Activity Monitor, each job hosted by the SQL Server instance is listed. The columns above the display fields can be used to sort the different jobs. Both the Filter link located in the status pane and the Filter button located at the top of the window can be used to filter the list of agent jobs.
Filter settings can be applied to each of the agent job columns. This capability is helpful when many jobs are listed. To apply a filter to the list of jobs, follow these steps:
1. From within the Job Activity Monitor, click the Filter button or the View Filter Settings link.
2. To configure the filter to show only failed jobs, select Failed from the Last Run Outcome drop-down.
3. When the filter is configured, enable the Apply Filter option near the bottom of the window.
4. Figure 21.1 shows how the filter settings should look when configured. Click OK to accept the settings.
Note
The filter icon changes from blue to green when a filter is applied to the list. To remove the filter, simply disable the Apply Filter option from within the Filter Settings dialog box.
The details window does not update automatically; however, you can configure it by selecting View Refresh Settings from the status pane. Note that the refresh interval and the filter settings are not persistent. When the Job Activity Monitor is closed, the settings revert back to the defaults.
The jobs shown in the details pane can also be managed. The right-click context menu allows you to start, stop, enable, disable, delete, and view the job history. You also can access the properties of the job by right-clicking the job and selecting Properties.
