Alerts for SQL Server (SAP Library

Alerts for SQL Server

Definition

Alerts for the database server indicate when threshold values that have been defined for the SQL Server have been exceeded. Existing threshold values reflect SAP recommendations for the database server, however they can be adjusted to meet individual system requirements.

Use

The database alerts, together with the R/3 monitoring functionality, help to optimize database administration. They reduce the amount of work involved by routinely checking the system for significant events and drawing attention to problems as soon as they arise. The amount of time that has to be invested for routine checks is significantly reduced and system inconsistencies can be eliminated more quickly.

Alerts marked in red indicate that there is a system problem that must be rectified. Alerts marked in yellow are warnings that indicate system events that might develop into problems later, if they are ignored.

Structure

The database alerts are displayed in the R/3 Alert Monitor template for the SQL Server. Together with other essential system information, the alerts are presented in the form of a tree that is subdivided into the following main categories:

Space Management
Performance
Backup Restore
R/3 Consistency
Health

The information provided can be viewed from different perspectives that offer varying levels of detail. The following tables summarize the information that is displayed in the Current Status view which is opened when you initially access the monitor. This view displays only the most recent alerts.

For information on other views, see The SQL Server Alert Monitor.

To find out the threshold values which trigger alerts in the system, see Viewing and Customizing Threshold Values.

Space Management

Status information and alerts related to disk space that is available for the server are displayed for both the <SAPSID> Database and the tempdb database. For each physical database file you can check the following:

Alert	Information Displayed
Autogrowth	Autogrowth setting. When autogrowth is enabled, the pre-allocated file size is automatically increased by a predefined number of megabytes whenever required.
Stats in MB	Space currently allocated for the file.
	Space already used for the file.
	Growth setting which is active. This is the amount of space automatically allocated to the file when it fills up.
Free disk space	The amount of free space left on the device where the file is located.

The values are refreshed every 8 hours.

Double-Clicking one of the alerts takes you to the Static Database Monitor (transaction DB02).

Performance

Factors which influence database performance and are automatically checked by the alert monitor are disk I/O, cache hit ratios and CPU usage.

Alert	Information Displayed
Disk Statistics	Double-clicking on the node displays information on the disk with the slowest response times and swap space.
CPU used by SQL	Percentage of CPU used by the SQL Server.
Data hit ratio	The total number of requests satisfied by the data cache divided by the total number of data cache accesses.
Procedure hit ratio	The total number of requests satisfied by the procedure cache divided by the total number accesses of the procedure cache.

The values are refreshed every 4 minutes.

Double-clicking one of the alerts takes you to the Operating System Monitor (transaction ST06).

Backup Restore

Information in this category assists the database administrator in determining whether the backup strategy is being implemented successfully.

Alert	Information Displayed
R/3 DB status	Date of last successful backup.
master DB status	Date of last successful backup.
msdb DB status	Date of last successful backup.
Backups running	Backups currently in progress.
Minutes since last backup	Time elapsed since the last backup.

Backup dates are refreshed once a day. The transaction log backup is checked every 4 minutes.

Double-clicking on an alert accesses the R/3 Backup Monitor (transaction DB12)

R/3 Consistency

This category of information shows the objects that are defined in the ABAP/4 Dictionary, but do not exist on the database.

Alert Item	Information Displayed
Tables	Number of tables defined in the ABAP/4 Dictionary that do not exist on the database.
Views	Number of views defined in the ABAP/4 Dictionary that do not exist on the database.
Indexes	Number of indexes defined in the ABAP/4 Dictionary that do not exist on the database.

Values are refreshed once a day.

Double-clicking on an alert accesses the Static Database Monitor (transaction DB02).

Health

The health of the system can be judged by monitoring the effectiveness of parameter settings and watching out for a number of common errors.

Alert	Information Displayed
SQL Server trace flags	Trace flags that have been set. Trace flags can be used to analyze different aspects of the SQL Server.
SQL Server memory setting	The type of SQL server memory setting that is active. Memory can have a fixed setting or vary within predefined limits.
Setup error	Error occurs when statistics are automatically computed for the VBHDR, VBDATA or VBMOD tables. In a SAP System, statistics for these tables must be disabled by setting the parameter sp_autostats to off.
Disk I/O error	Number of disk read or write errors that have been detected.
Network packet error	Errors detected during the transmission of data packets through the network.
Error log messages	Total number of error messages that have a severity level of 17 or higher. Errors of these levels must be resolved by the system administrator.

Double-clicking on an alert takes you to the SAP/SQL Server Database Monitor (transaction ST04).