sql server查看表空间

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

http://www.2cto.com/database/201206/135777.html

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

http://wenku.baidu.com/link?url=NQE-ljyvkW4U8I6mSEpYLpj9dU-qXDh9LueQ1kXJhTg8vtQkCeKaUJ6u0ieB67Ym1A47wUoP0P0NGo5vYoaQVSCzcUkpADeqSwWg4i696hK