sql server查看表空间语句
方法一:
select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes
where indid=1
order by reserved desc
方法二:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) , www.2cto.com
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END www.2cto.com
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注:显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
SQLServer数据库在使用时间久之后,往往数据文件及日志文件会变得很大,有时候甚至高达十几G,这个时候一般做法是裁断日志以缩小日志文件、归档历史记录到新数据库或备份文件以便释放空间,然后执行收缩数据库命令来缩小数据文件大小,然而收缩命令经常并不会有太大的作用,因为收缩是以区为单位进行的,并不会对页进行整理。如果数据库上有很多碎片,如一个可容纳8个页的区实际上只存放1个页,就会造成大量磁盘空间浪费并且无法收缩。
SQLServer磁盘碎片整理的目的在于,通过重建聚集索引或重建堆自增列的方式,以页为单位进行磁盘空间整理,然后再使用收缩命令收缩数据库文件,真正意义上实现了数据库的完全收缩。
以下存储过程在MSSQL2008上测试通过,由于需要读写操作和表锁定,请避免在数据库繁忙时段运行。
/* 读取磁盘分区信息 */
CREATE PROCEDURE SP_ExtentInfo
AS
DBCC ExtentInfo(0)
GO
/* SQLServer磁盘碎片整理 */
CREATE PROCEDURE SP_ShrinkSpaces
(
@UsagePercent numeric(2,2) = 0.60
--整理小于指定使用率的表空间,1为100%使用率无需整理
)
AS
BEGIN
--创建保存分区信息的临时表
Create Table #ExtentInfo
(
fileid smallint,
pageid int,
pg_alloc int,
ext_size int,
obj_id int,
index_id int,
partition_number int,
partition_id bigint,
iam_chain_type varchar(50),
pfs_bytes varbinary(10)
)
insert into #ExtentInfo exec SP_ExtentInfo
--使用游标,对小于指定空间使用率的表进行整理
declare @Table sysname
declare @Index sysname
declare @IdentityName sysname
declare @sql varchar(1000)
declare cs cursor for
select (select name from sysobjects where id=obj_id and xtype='u'), --xtype='u'的记录为数据表
(select name from sysindexes where id=obj_id and indid=1) --indid=1的记录为聚集索引
from #ExtentInfo group by obj_id
having sum(pg_alloc)*1.0/max(ext_size)/count(*)<=@UsagePercent
open cs
fetch next from cs into @Table,@Index
while @@FETCH_STATUS=0
begin
if @Table is not null
begin
if @Index is not null
begin
--重建聚集索引
set @sql = 'alter index ' + @Index + ' on ' + @Table + ' rebuild'
print @sql
exec(@sql)
end
else
begin
--对于堆,清空并重新写表或给自增列加聚集索引(128代表自增列)
select @IdentityName=name from syscolumns where id=OBJECT_ID(@Table) and status=128
if @@ROWCOUNT=0
set @sql = 'select * into #ExtentTable from ' + @Table + ' truncate table ' + @Table + ' insert ' + @Table + ' select * from #ExtentTable'
else
set @sql = 'create clustered index ExtentOperaPrimaryKey on ' + @Table + '(' + @IdentityName + ') drop index ' + @Table + '.ExtentOperaPrimaryKey'
print @sql
exec(@sql)
end
end
fetch next from cs into @Table,@Index
end
close cs
deallocate cs
--收缩当前数据库
DBCC SHRINKDATABASE(0)
--重新获取分区信息
truncate table #ExtentInfo
insert into #ExtentInfo exec SP_ExtentInfo
--显示当前分区信息
select fileid,obj_id,index_id,partition_id,ext_size,
object_name(obj_id) as '对象名',
count(*) as '实际区数', sum(pg_alloc) as '实际页数',
ceiling(sum(pg_alloc)*1.00/ext_size)*ext_size as '最大可用页数',
ceiling(sum(pg_alloc)*1.00/ext_size*100.00/count(*)) as '表空间使用比率'
from ExtentInfo
group by fileid,obj_id,index_id,partition_id,ext_size
order by partition_id,obj_id,index_id,fileid
--删除临时表
drop table #ExtentInfo
END