sp_spaceused (Transact-SQL)
显示行数、保留的磁盘空间以及当前数据库中的表、索引视图或 Service Broker 队列所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间。
|
适用范围：SQL Server（SQL Server 2008 到当前版本）。
|
 Transact-SQL
语法约定
sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]
- [ @objname=] 'objname'
-
请求其空间使用信息的表、索引视图或队列的限定或非限定名称。 仅当指定限定对象名称时，才需要使用引号。 如果提供完全限定对象名称（包括数据库名称），则数据库名称必须是当前数据库的名称。
如果未指定 objname，则返回整个数据库的结果。
objname 的数据类型为 nvarchar(776)，默认值为 NULL。
- [ @updateusage=] 'updateusage'
-
指示应运行 DBCC UPDATEUSAGE 以更新空间使用信息。 当未指定 objname 时，将对整个数据库运行该语句；否则，将对 objname 运行该语句。 值可以为 true 或 false。updateusage 的数据类型为 varchar(5)，默认值为 false。
如果省略 objname ，将返回以下结果集，以提供当前数据库大小信息。
|
列名
|
数据类型
|
说明
|
|---|
|
database_name
|
nvarchar(128)
|
当前数据库的名称。
|
|
database_size
|
varchar(18)
|
当前数据库的大小 (MB)。 database_size 包括数据和日志文件。
|
|
unallocated space
|
varchar(18)
|
未保留供数据库对象使用的数据库空间。
|
|
列名
|
数据类型
|
说明
|
|---|
|
reserved
|
varchar(18)
|
由数据库中对象分配的空间总量。
|
|
数据
|
varchar(18)
|
数据使用的空间总量。
|
|
index_size
|
varchar(18)
|
索引使用的空间总量。
|
|
unused
|
varchar(18)
|
为数据库中的对象保留但尚未使用的空间总量。
|
如果指定 objname，则将为指定对象返回以下结果集。
|
列名
|
数据类型
|
说明
|
|---|
|
name
|
nvarchar(128)
|
请求其空间使用信息的对象的名称。
不返回对象的架构名称。 如果需要架构名称，请使用 sys.dm_db_partition_stats 或 sys.dm_db_index_physical_stats 动态管理视图获取等价大小信息。
|
|
rows
|
char(11)
|
表中现有的行数。 如果指定的对象是 Service Broker 队列，该列将指示队列中的消息数。
|
|
reserved
|
varchar(18)
|
为 objname 保留的空间总量。
|
|
数据
|
varchar(18)
|
objname 中的数据所使用的空间总量。
|
|
index_size
|
varchar(18)
|
objname 中的索引所使用的空间总量。
|
|
unused
|
varchar(18)
|
为 objname 保留但尚未使用的空间总量。
|
database_size 将始终大于 reserved + unallocated_space 之和，因为该值包括日志文件的大小，而 reserved 和 unallocated_space 只考虑数据页。
在这两个结果集的 index_size 中，都包括了 XML 索引和全文索引使用的页。 当指定 objname 时，对象的 XML 索引和全文索引所使用的页将计算在 reserved 和 index_size 结果中。
如果为具有空间索引的数据库或对象计算空间使用情况，则空间大小列（例如，database_size、 reserved 和 index_size）将包含空间索引的大小。
指定 updateusage 时，SQL Server 数据库引擎将扫描数据库中的数据页，并根据每个表所使用的存储空间对 sys.allocation_units 和 sys.partitions 目录视图进行必要的更正。 在某些情况下（例如删除索引后、表的空间信息不是当前信息时），需要执行该操作。 updateusage 在大型表或数据库上运行会花费一些时间。 只有当怀疑所返回的值不正确，而且该进程对数据库中的其他用户或进程没有负面影响时，才应使用 updateusage。 如果首选该进程，则可以单独运行
DBCC UPDATEUSAGE。
|
注意 |
|---|
|
在删除或重新生成大型索引时，或者在删除或截断大型表时，数据库引擎将延迟实际页释放及其关联锁，直至事务提交完毕为止。 延迟的删除操作不会立即释放已分配的空间。 因此，删除或截断一个大型对象后 sp_spaceused 随即返回的值可能不会影响可用的实际磁盘空间。
|
执行 sp_spaceused 的权限授予 public 角色。 只有 db_owner 固定数据库角色的成员可以指定 @updateusage 参数。
A.显示表的磁盘空间信息
以下示例报告 Vendor 表及其索引的磁盘空间信息。
USE AdventureWorks2012;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO
B.显示数据库的已更新空间信息
下例对当前数据库中使用的空间进行了汇总，并使用可选参数 @updateusage 确保返回当前值。
USE AdventureWorks008R2;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO
-----------------------------------------------------
create proc [dbo].[spaceused]
as
begin
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5) -- Param. for specifying that
create table #temp1
(
表名 varchar(200) null,--表名
数据行数 char(11) null,--行数
保留空间 varchar(15) null,--保留空间
数据占用空间 varchar(15) null,--数据使用空间
索引占用空间 varchar(15) null,--索引使用空间
空闲空间 varchar(15) null--未用空间
)
--select @objname=''N_dep'' -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
declare cur_table cursor for
select name from sysobjects where type='u'
Open cur_table
fetch next from cur_table into @objname
While @@FETCH_STATUS=0
begin
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ''
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = ''E'' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + 'MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ''
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end
/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
insert into #temp1
select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
Drop table #spt_space
end
fetch next from cur_table into @objname
end
Close cur_table
DEALLOCATE cur_table
Select * from #temp1 order by len(保留空间) desc,保留空间 desc
Drop table #temp1
return (0)
end
GO
select * into master..业务变更附件 from 业务变更附件
-------------------------------------------
truncate table dbo.业务变更附件
SET IDENTITY_INSERT dbo.业务变更附件 ON
insert into 业务变更附件(id,name,受理ID,document)
select id,name,受理ID,document from master.dbo.业务变更附件
SET IDENTITY_INSERT dbo.业务变更附件 off
-----------------------------------------------
drop table master.dbo.业务变更附件
钟*
truncate 再重新插入
钟*
那些多余的空间 就没有了
|
转播
