sp_spaceusage

Reports the space usage for a table, index, or transaction log and estimates the amount of fragmentation for tables and indexes in a database. The estimates are computed using an average row-length for data and index rows, and the number of rows in a table. You can archive the space usage and fragmentation data for future reporting and trends analysis. sp_spaceusage supports a number of actions, including help, display, archive and report, to indicate the current SAP ASE space usage.

Syntax

The “help” action syntax:

sp_spaceusage   'help'[, 'all']

sp_spaceusage 'help' [, {'display' |  'display summary'
		| 'report' | 'report summary' | 'archive'} 
		[, {'table' | 'index' | 'tranlog'}]]

The “display” action syntax:

sp_spaceusage 'display summary [using unit= {KB | MB | GB | PAGES} ]', 
	{'table' | 'index'}, name
	[,where_clause [,order_by[,command ] ] ]

sp_spaceusage 'display [using unit= {KB | MB | GB | PAGES} ]',
	{'table' | 'index'}, name 
	[,select_list 
	[,where_clause [,order_by[,command] ] ] ]

sp_spaceusage 'display [using unit={KB | MB | GB | PAGES} ]', 
	'tranlog' [, name[,select_list[,where_clause [,order_by]]]]

The “archive” action syntax:

sp_spaceusage 'archive [ using_clause ]', 
	{'table' | 'index'}, name[,where_clause[,command] ]

sp_spaceusage 'archive [ using_clause ]',
	'tranlog' [,name[,where_clause] ]

The “report” action syntax:

sp_spaceusage 'report summary [ using_clause ]', 
	{'table' | 'index'}, name 
	[,where_clause [,order_by[,from_date [,to_date]]]]

sp_spaceusage 'report [ using_clause ]', 
	{'table' | 'index'}, name 
	[,select_list[,where_clause [,order_by[,from_date [,to_date]]]]]

sp_spaceusage 'report [ using_clause ]', 
	'tranlog' [, name 
	[,select_list[,where_clause [,order_by 
	[,from_date [,to_date]]]]]]
using_clause = USING using_item [, using_item ...]

using_item = { unit={ KB | MB | GB | PAGES } 
	| dbname=database_name | prefix=string }

Parameters

help – displays the entire sp_spaceusage syntax. help action displays the syntax for individual actions supported..
display – displays current space usage information for the specified objects.
display summary – displays a summary of current space usage information for the specified objects.
archive – archives the space usage report to a table. If the archive table does not already exist, sp_spaceusage creates one. New data is appended to existing data. You can specify a prefix for the archive table name and the database in which the archive table resides with the using clause.
report – reports the space usage information for the specified objecs from previously archived data. The output is same as the display action. Include the optional using clause to specify the archive table.
report summary – reports a summary of space usage information for the specified objecs from previously archived data. The output is same as the display summary action. Include the optional using clause to specify the archive table.
using_item – specifies the unit, archive database name, and prefix string for the archive table. You can use a unit size of kilobytes (KB), megabytes (MB), gigabytes (GB), and pages. By default unit size is KB, the current database is the archive database, and no prefix string is assumed.
name – is the name of the entity. Depending on the entity type, you can include multipart names such as owner_name.table_name, or owner_name.table_name.index_name. For the entity type tranlog, the name must be syslogs or NULL. Pattern specifiers are allowed for each part of a multipart name to support reporting on multiple objects in one pass.
select_list – is the comma-separated list of columns to select in the output columns for the display and report actions. Use * to include all columns in the output. Columns can be renamed using the alias=name notation.
where_clause – is the filter to apply to the result set. Use with the display, report, or archive actions to selectively filter unnecessary data.
order_by – returns query results in the specified columns in sorted order.
command – command run on the entity selected (table, column, or so on) prior to gathering the space usage information for qualifying objects. The following commands are supported: update statistics, update table statistics, and update index statistics.
from_date – specifies beginning of the time range you are interested in.
to_date – specifies end of the time range you are interested in.

Examples

Example 1 – Displays a brief description, syntax, and usage information for the display action:

sp_spaceusage 'help', 'display'

Display the space usage information for an entity in the current database.

Usage:
sp_spaceusage 'display', {'table'|'index'}, <name>
              [,<select_list> [,<where_clause> [,<order_by> [,<command>]]]]

sp_spaceusage 'display summary', {'table'|'index'}, <name>
              [,<where_clause> [,<order_by> [,<command>]]]

sp_spaceusage 'display', 'tranlog' [,{'syslogs'|NULL}
              [,<select_list> [,<where_clause> [,<order_by>]]]]

For more information, use:
sp_spaceusage 'help', 'display', 'table'
sp_spaceusage 'help', 'display', 'index'
sp_spaceusage 'help', 'display', 'tranlog'

Example 2 – Displays a summary of the space usage on the titles table:

sp_spaceusage 'display summary', 'table', 'titles'

All the page counts in the result set are in the unit 'KB'.
OwnerName  TableName  Type   UsedPages  RsvdPages  ExpRsvdPages  PctBloatRsvdPages
---------  ---------  ----   ---------  --------   ------------  -----------------
dbo        titles     DATA   6.0        30.0       16.0          87.50
dbo        titles     INDEX  8.0        64.0       32.0          50.00

Example 3 – Displays the space usage information for the titles table:

sp_spaceusage 'display', 'table', 'titles'

All the page counts in the result set are in the unit 'KB'.
OwnerName    TableName    IndId    NumRows    UsedPages    RsvdPages
  ExtentUtil     ExpRsvdPages    PctBloatUsePages     PctBloatRsvdPages
-----------  -----------  ------  -----------  ----------   --------------
  ----------     ------------    ----------------     -----------------
dbo          titles       0       18.0         6.0          30.0
  20.00          16.0            0.0                  87.50 
dbo          titles       1       NULL         4.0          32.0
  12.50          16.0            0.00                 100.00
dbo          titles       2       NULL         4.0          32.0
  12.50          16.0            0.00                 100.00

Example 4 – Displays the space usage information, in megabytes, for all indexes on the titles table with names that start with title:

sp_spaceusage 'display using unit-MB', 'index', 'titles.title%'

All the page counts in the result set are in the unit 'MB'.
OwnerName  TableName    IndId  IndexName   UsedPages       RsvdPages
  ExtentUtil   ExpRsvdPages     PctBloatUsedPages   PctBloatRsvdPages
--------   --------     -----  ----------  ----------      ---------
  ----------   ------------     -----------------   ----------------
dbo        titles        0     titles      .005859375     .029296875 
  20.00        .015625           0.00               87.50
dbo        titles       1      titleidind  .00390625      .03125                
  12.50        .015625           0.00               100.00
dbo        titles       2      titleind    .00390625      .03125
  12.50        .015625           0.00               100.00
(1 row affected)
(return status = 0)

Example 5 – Displays a summary of the space usage for all index names starting with title in the titles table:

sp_spaceusage 'display summary', 'index', 'titles.title%'

All the page counts in the result set are in the unit of 'KB'.
OwnerName TableName  IndexName   IndId  UsedPages  RsvdPages  ExpRsvdPages
    PctBloatRsvdPages
--------  ---------  ---------   ----- ---------  ----------  ------------
    ----------------- 
dbo       titles     titles      0     6.0        30.0      16.0
    46.67
dbo       titles     titleidind  1     4.0        32.0      16.0
    50.00

dbo       titles     titleind    2     4.0        32.0      16.0
    50.00

Example 6 – Displays a summary of the space usage for all indexes starting with title in the titles table where the value of PctBloatRsvdPages is less than 50:

sp_spaceusage 'display summary', 'index', 'titles.title%', 
    'where PctBloatRsvdPages < 50'

All the page counts in the result set are in the unit 'KB'.
OwnerName  TableName  IndexName  IndId  UsedPages  RsvdPages  ExpRsvdPages
     PctBloatRsvdPages
---------  ---------  ---------  -----  ---------  ---------  ----------
     -----------------
dbo        titles     titles     0      6.0        30.0         16.0
     46.67

Example 7 – Displays a summary of the space usage for all indexes in the titles table in descending order of PctBloatRsvdPages where the value of PctBloatRsvdPages is greater than 30:

1> sp_spaceusage 'display summary', 'index', 'titles.title%', 
   'where PctBloatRsvdPages > 30', 'order by PctBloatRsvdPages desc'

All the page counts in the result set are in the unit 'KB'.
OwnerName  TableName  IndexName  IndId  UsedPages  RsvdPages  ExpRsvdPages
     PctBloatRsvdPages
---------  ---------  ---------  -----  ---------  ---------  ----------
     -----------------
dbo        titles     titleidind  1     4.0        32.0       16.0
     50.00
dbo        titles     titleind    2     4.0        32.0       16.0
     50.00
dbo        titles     titles      0     6.0        30.0       16.0
     46.67

Example 8 – Runs update table statistics on the authors table and summarizes its space usage information in the unit pages:

sp_spaceusage 'display summary using unit=pages', 'table', 'authors', null, null, null,
 'update table statistics'

All the page counts in the result set are in the unit 'pages'.
OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages
--------- --------- ---- --------- --------- ------------ -----------------
dbo        authors  DATA    2.0          16.0         8.0         100.00
dbo        authors  INDEX   4.0          32.0         16.0         50.00

Example 9 – Displays the space usage information for the transaction log of the current database (pubs2):

sp_spaceusage 'display', 'tranlog'

TableName TotalPages UsedPages CLRPagesFreePages PctUsedPages PctFreePages
--------- ----------- --------- ----------------- ------------- ----------- 
syslogs   4096.0      18.0 0.0  1482.0             0.43         36.18

Example 10 – Archives the space usage information for the authors table in the currrent database into the default table (spaceusage_object for tables and indexes):
```
sp_spaceusage 'archive', 'table', 'authors'
```
```
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
```
Example 11 – Archives the space usage information for the authors table into the default table (spaceusage_object for tables and indexes) in the pubs3 database:
```
sp_spaceusage 'archive using dbname = pubs3', 'table', 'authors'
```
```
Data was successfully archived into table 'pubs3.dbo.spaceusage_object'.
```
Example 12 – Runs update table statistics on the authors table and archives its space usage information into a table in the current database with the prefix monday_ (for this example, monday_spaceusage_object):
```
1> sp_spaceusage 'archive using dbname = pubs2, prefix=monday_', 
  'table','authors', null, 'update table statistics'
```
Example 13 – Archives the space usage information for the transaction log of the current database into the default table (spaceusage_tranlog for transaction logs) in the pubs3 database:
```
sp_spaceusage 'archive using dbname=pubs3', 'tranlog'
```
```
Data was successfully archived into table 'pubs3.dbo.spaceusage_tranlog'.
```

Example 14 – Reports in detail the last archived space usage information for the authors table from the default table (spaceusage_object for table or index) in the current database:

sp_spaceusage 'report', 'table', 'authors'

All the page counts in the result set are in the unit 'KB'.
All the data in the result set are dated 'Jun 15 2013 11:50PM'.
OwnerName  TableName  IndId  NumRows  UsedPages  RsvdPages  ExtentUtil
   ExpRsvdPages    PctBloatUsedPages    PctBloatRsvdPages
---------  ---------  -----  -------  ---------  ---------  ----------
   ------------    -----------------    -----------------
dbo        authors    0      23.0     4.0        32.0       12.50
   16.0            0.00                 100.00
dbo        authors    1      NULL     4.0        32.0       12.50
   16.0            0.00                 100.00
dbo        authors    2      NULL     4.0        32.0       12.50
   16.0            0.00                 100.00
(1 row affected)(return status = 0)

Example 15 – Reports in summary the last archived space usage information for the authors table from the default table in the pubs3 database:

sp_spaceusage 'report summary using dbname=pubs3', 'table', 'authors'

All the page counts in the result set are in the unit 'KB'.
All the data in the result set are dated 'Jan 17 2013 11:29AM'.
OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages
--------- --------- ---- ---------- -------- ------------ -----------------
dbo       authors   DATA   4.0    32.0      16.0         100.00
dbo       authors   INDEX  8.0    64.0      32.0         50.00

Example 16 – Reports a summary from the monday_spaceusage_object table in the current database the last archived space usage information (in megabytes) for the authors table:

sp_spaceusage 'report summary using prefix=monday_, unit=MB', 'table', 
    'authors'

All the page counts in the result set are in the unit 'MB'.
All the data in the result set are dated 'Jan 17 2013 11:29AM'.
OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages
--------- --------- ---- ---------- -------- ------------ -----------------
dbo       authors   DATA   .00390625  .03125   .015625   100.00
dbo       authors   INDEX   .0078125   .0625    .03125   50.00

Example 17 – Reports the space usage information from the default table in the current database for all the indexes on the authors table archived on Jun 9, 2013 or later:

sp_spaceusage 'report', 'index', 'authors.%', null, null, null, 'Jun 9 2013'

All the page counts in the result set are in the unit 'KB'.
ArchiveDateTime      OwnerName  TableName   IndId  IndexName  UsedPages
  RsvdPages  ExtentUtil  ExpRsvdPages  PctBloatUsedPages   PctBloatRsvdPages
---------------      ---------  ---------   -----  ---------  ----------
  ---------  ---------   -----------  ----------------    --------------
Jun  9 2013 12:06AM   dbo       authors      0     authors    4.0    
  32.0       12.50       16.0         0.00  100.00
Jun 10 2013 12:05AM  dbo        authors      0     authors    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 11 2013 11:35PM  dbo        authors      0     authors    4.0
  32.0       12.50       16.0         0.00 100.00
Jun  9 2013 12:06AM  dbo        authors      1     auidind    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 10 2013 12:05AM  dbo        authors      1     auidind    4.0
  32.0       12.50       16.0         0.00  100.00
Jun 11 2013 11:35PM  dbo        authors      1     auidind    4.0 
  32.0       12.50       16.0         0.00  100. 
Jun  9 2013 12:06AM  dbo        authors      2     aunmind    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 10 2013 12:05AM  dbo        authors      2     aunmind    4.0  
  32.0       12.50       16.0         0.00  100.00
Jun 11 2013 11:35PM  dbo        authors      2     aunmind    4.0
  32.0       12.50        16.0        0.00  100.00
(1 row affected)
(return status = 0)

Example 18 – Reports the space usage information for the authors table from the default table in the current database archived between Jun 10 2013 and Jun 15 2013:

sp_spaceusage 'report', 'table', 'authors', null, null, null, 'Jun 10 2013', 'Jun 15 2013'

All the page counts in the result set are in the unit 'KB'.
ArchiveDateTime      OwnerName TableName  IndId  NumRows     UsedPages
 RsvdPages  ExtentUtil  ExpRsvdPages  PctBloatUsedPages  PctBloatRsvdPages
---------------      --------- ---------  -----  -------     --------- 
 ---------  ----------   ------------   -----------------   --------------
Jun 10 2013 12:05AM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2013 11:35PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2013 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 10 2013 12:05AM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2013 11:35PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2013 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 10 2013 12:05AM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2013 11:35PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2013 11:46PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2013 11:46PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00

(1 row affected)
(return status = 0)

Usage

sp_spaceusage provides space usage information for tables, indexes, and the transaction log of the current database.
The database in which you are archiving the space usage data must have sp_dboption ... select into enabled.
The archive tables are created if they do not already exist at the time of archiving, otherwise the results are appended to the current table. Because of this, any user running sp_spaceusage must have create table permission in the archive database.
While archiving or reporting data, only tables owned by the user running sp_spaceusage are considered for the archive table. Tables with the same name but owned by another user are ignored. By default, the results are archived to or reported from the spaceusage_object table for tables or indexes and spaceusage_tranlog for the transaction log. .
You can use the from_date and to_date arguments only for the report action when reporting from archived data. The SAP ASE server uses only the data in the archive table that falls within the specified time-range when generating the report. If you do not include a from_date or a NULL, the SAP ASE server uses all archived data prior to the to_date. If you do not include a to_date or NULL, the SAP ASE server uses the current date as the value for to_date. If you do not include either the from_date or to_date, the SAP ASE server uses the most recent data in the archive table to generate the report.
sp_spaceusage results are estimated based on statistical data. These estimates are only as good the statistics provided. You can run update statistics to improve the accuracy of the results.

Permissions

Any user can execute sp_spaceusage to view space usage. However, you may not be able to view certain information about tables that you do not have persmissions to view.