sp_logging_rate

Syntax

sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, interval='hh:mm:ss' | clear_option='y'|'n']

Parameters

• full – sp_logging_rate provides a detailed report for each collection.
• sum – sp_logging_rate provides summary information, including values for the average, minimum, maximum, and the maximum rate. If you do not specify a time, sp_logging_rate collects information every 10 seconds.
• day, hh:mm:ss – Specifies the duration of time sp_logging_rate runs, using the form date, hour:minute:second.
• interval = 'hh:mm:ss' – Period of time during which the interval runs, using the form hour:minute:second
• clear_option = 'y' | 'n" – Determines whether to clear the monitor counters during data collection.

Examples

• Example using sum parameter – sp_logging_rate collects information for 1 day and 8 hours, takes a sample every 10 minutes, and prints summary information at the end of the interval:

  sp_logging_rate 'sum', '1,08:00:00', '00:10:00'
  =========================
  Total Summary Information
  =========================
  Transaction Log Growth Rate      Min GB/h        Max GB/h        Avg GB/h
  ---------------------------   --------------  --------------  --------------
                                    0.000000        2.870076        1.823028

• Example using full parameter – sp_logging_rate collects information for 3 minutes, takes samples every 10 seconds (the default), and prints summary information at the end of the interval:

  sp_logging_rate 'full', '00:03:00'
  Date Time                       Transaction Log Growth Rate GB/h
  ---------------------------     --------------------------------
  Oct 22 2013  6:00:32:480AM                 0.406779
  
  
  Oct 22 2013  6:00:42:483AM                 0.000000
  
  
  Oct 22 2013  6:00:52:483AM                 0.000000
  
  
  Oct 22 2013  6:01:02:483AM                 0.000000
  
  
  Oct 22 2013  6:01:12:490AM                 0.000000
  
  
  Oct 22 2013  6:01:22:500AM                 0.000000
  
  
  Oct 22 2013  6:01:32:476AM                 2.341870
  
  
  Oct 22 2013  6:01:42:483AM                 2.828132
  
  
  Oct 22 2013  6:01:52:480AM                 2.850305
  
  
  Oct 22 2013  6:02:02:483AM                 2.782750
  
  
  Oct 22 2013  6:02:12:483AM                 2.853574
  
  
  Oct 22 2013  6:02:22:480AM                 2.002917
  
  
  Oct 22 2013  6:02:32:483AM                 2.848995
  
  
  Oct 22 2013  6:02:42:483AM                 2.754143
  
  
  Oct 22 2013  6:02:52:483AM                 2.854949
  
  
  Oct 22 2013  6:03:02:480AM                 2.722928
  
  
  Oct 22 2013  6:03:12:476AM                 2.870076
  
  
  Oct 22 2013  6:03:22:480AM                 2.697094
  
  
  
  =========================
  Total Summary Information
  =========================
  Transaction Log Growth Rate      Min GB/h        Max GB/h        Avg GB/h
  ---------------------------   --------------  --------------  --------------
                                    0.000000        2.870076        1.823028

Usage

• You cannot run scripts or procedures that collect monitoring data (for example, sp_sysmon) while sp_logging_rate runs. Because sp_logging_rate collects and clears monitor counter as it runs, the monitoring counter information these scripts or procedures collect will not be accurate.
• sp_logging_rate produces unreliable results if you specify an amount of time for interval = 'hh:mm:ss' that is greater than the amount of time you specify for 'day, hh:mm:ss'.
• When you specify values for interval = 'hh:mm:ss' and 'day, hh:mm:ss', keep in mind:
  • If the value you specify for interval = 'hh:mm:ss' is greater than the value you specify for 'day, hh:mm:ss', SAP ASE issues an error message and sp_logging_rate produces no result set.
  • sp_logging_rate may produce an unreliable result if that ratio for 'day, hh:mm:ss' to interval = 'hh:mm:ss' is too small. For example,  if you specify day,00:10:00, and interval='00:04:00', sp_logging_rate collects only two values, prints an average value, with the first value as the maximum, and the second value as the minimum. A better ratio produces a more reliable result set.

Permissions

You must have system administrator privileges to execute sp_logging_rate.