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.

See also Viewing and Customizing Threshold Values.

Use

The database alerts, together with the SAP 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 SAP Alert Monitor 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

·        DDIC 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 active 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.

·         To find out the meaning of the various icons on the screen choose Extras ® Legend.

·         To display the threshold values that trigger alerts, select an alert, press the right mouse button and choose Properties.

Space Management

Status information and alerts related to the 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 by expanding the tree nodes:

Alert

Information Displayed

Autogrowth

Autogrowth setting.
When autogrowth is enabled, the pre-allocated file size is automatically increased by a predefined number of megabytes, which can be expressed as a percentage, as required. If the file, and all files on the device, have enough room to grow, this alert is normal

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 Database Performance: Database Allocation (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

Indicates whether functions for collecting disk statistics are active. If they are active, you can access them by opening the Operating System Monitor (transaction ST06) with a double-click. Then choose Detail Analysis Menu and subsequently Disk.

The millisecs/access for each file is also shown. The values shown represent the performance of the disk over the last 20 minutes, which corresponds to the frequency with which the sap_perf job is run.

CPU used by SQL

Percentage of CPU used by the SQL Server. The alert indicates when the SQL Server is reaching the limits of the CPU capacity available for it.
For more information, double-click to open the Database Performance Analysis transaction. Click Current Activity.

Data hit ratio

The total number of requests satisfied by the data cache divided by the total number of data cache accesses.
For more information double-click to open Database Performance Analysis. Click Detail Analysis Menu
® Server Details and then look under Cache Detail.

Procedure hit ratio

The total number of requests satisfied by the procedure cache divided by the number accesses of the procedure cache.
To find out more, double-click to open Database Performance Analysis. Click Detail Analysis Menu
® Server Details and then look under Cache Detail.

Values are refreshed every 4 minutes.

Backup Restore

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

Alert

Information Displayed

DB status

Days since last backup.

master DB status

Days since last backup.

msdb DB status

Days since last backup.

Backups running

Backups currently in progress.

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

Double-clicking on an alert accesses the Backup Restore Information (transaction DB12).

DDIC Consistency

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

Alert Item

Information Displayed

Tables

Number of tables defined in the ABAP Dictionary that do not exist on the database.

Views

Number of views defined in the ABAP Dictionary that do not exist on the database.

Indexes

Number of indexes defined in the ABAP Dictionary that do not exist on the database.

Values are refreshed once a day.

Double-clicking on an alert accesses Database Performance: Database Allocation (transaction DB02). For details, click DB Analysis ® Database <-> ABAP Dictionary ® Display.

Health

Monitoring the effectiveness of parameter settings and watching out for a number of common errors can judge the health of the system.

Alert

Information Displayed

SQL Server Build Data

The alert shows the build number, whether SQL Server is clustered, and if AWE is enabled.

OS Build Data

The alert shows the Windows build, the OS version and the chip type.

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.

SQL Server Setup

A variety of conditions are checked including SQL Server configuration, update table customization, and dbsl dll file version usage.

Disk I/O

Number of disk read or write errors that have been detected.

Network packet

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. The system administrator must resolve errors of these levels. To view logs, double-click to open Database Performance Analysis. Click Detail Analysis Menu ®  Error Logs. To view a log, select it, enter the no. of lines to read and press enter.

Double-clicking on an alert takes you Database Performance Analysis (transaction ST04).