Chapter 1: System Procedures

sp_clearstats

Description

Initiates a new accounting period for all server users or for a specified user. Prints statistics for the previous period by executing sp_reportstats.

Syntax

sp_clearstats [loginame]

Parameters

loginame: is the user’s login name.

Examples

Example 1

Initiates a new accounting period for all users.

sp_clearstats

Name    Since           CPU  Percent CPU     I/O    Percent I/O 
------  --------     ------  ----------- -------    ------------- 
probe   Jun 19 1990       0           0%       0         0% 
julie   Jun 19 1990   10000     24.9962%    5000         24.325% 
jason   Jun 19 1990   10002     25.0013%    5321         25.8866% 
ken     Jun 19 1990   10001     24.9987%    5123         24.9234%
kathy   Jun 19 1990   10003     25.0038%    5111         24.865% 
(5 rows affected) 
Total CPU   Total I/O 
---------      --------- 
40006       20555 
5 login accounts cleared.

Example 2

Initiates a new accounting period for the user “kathy.”

sp_clearstats kathy

Name  Since         CPU     Percent CPU     I/O     Percent I/O 
----- -----------   -----   ------------    -----   ----------- 
KATHY Jul 24 1990   498     49.8998%        483924  9.1829% 
(1 row affected)
Total CPU   Total I/O 
---------   ---------- 
998         98392 
1 login account cleared.

Usage

sp_clearstats creates an accounting period and should be run only at the end of a period.
Because sp_clearstats clears out the accounting statistics, you must record the statistics before running the procedure.
sp_clearstats updates the syslogins field accdate and clears the syslogins fields totcpu and totio.

Permissions

Only a System Administrator can execute sp_clearstats.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect