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

sp_spaceusage supports a variety of operations.

sp_spaceusage 

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

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 '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)

Example15 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 run sp_spaceusage. However, they may not be able to view certain information about tables that they do not have persmissions to view.