sp_spaceusage

Description

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 Adaptive Server 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 whose names 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 2007 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 2007 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 2007 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, 2007 or later:

sp_spaceusage 'report', 'index', 'authors.%', null, null, null, 'Jun 9 2007'
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 2007 12:06AM   dbo       authors      0     authors    4.0    
  32.0       12.50       16.0         0.00  100.00
Jun 10 2007 12:05AM  dbo        authors      0     authors    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 11 2007 11:35PM  dbo        authors      0     authors    4.0
  32.0       12.50       16.0         0.00 100.00
Jun  9 2007 12:06AM  dbo        authors      1     auidind    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 10 2007 12:05AM  dbo        authors      1     auidind    4.0
  32.0       12.50       16.0         0.00  100.00
Jun 11 2007 11:35PM  dbo        authors      1     auidind    4.0 
  32.0       12.50       16.0         0.00  100. 
Jun  9 2007 12:06AM  dbo        authors      2     aunmind    4.0 
  32.0       12.50       16.0         0.00  100.00
Jun 10 2007 12:05AM  dbo        authors      2     aunmind    4.0  
  32.0       12.50       16.0         0.00  100.00
Jun 11 2007 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 2007 and Jun 15 2007:

sp_spaceusage 'report', 'table', 'authors', null, null, null, 'Jun 10 2007', 'Jun 15 2007'
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 2007 12:05AM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2007 11:35PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2007 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 11:46PM  dbo       authors    0      23.0        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 10 2007 12:05AM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2007 11:35PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2007 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 11:46PM  dbo       authors    1      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 10 2007 12:05AM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 11 2007 11:35PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 13 2007 11:46PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 11:46PM  dbo       authors    2      NULL        4.0
 32.0       12.50      16.0          0.00                100.00
Jun 14 2007 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

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.