sql server查看表空间

来源：互联网发布：软件需求管理编辑：程序博客网时间：2024/05/29 07:47

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=1order 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.namefrom dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1and o.name not like N'#%%' order by o.name OPEN Info_cursorFETCH NEXT FROM Info_cursorINTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN 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 = @tablenameFETCH NEXT FROM Info_cursorINTO @tablenameEND  www.2cto.com  CLOSE Info_cursorDEALLOCATE Info_cursorGO --itlearner注：显示数据库信息sp_spaceused @updateusage = 'TRUE' --itlearner注：显示表信息select *from tablespaceinfoorder 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_ExtentInfoAS        DBCC ExtentInfo(0)GO/* SQLServer磁盘碎片整理 */CREATE PROCEDURE SP_ShrinkSpaces(        @UsagePercent numeric(2,2) = 0.60        --整理小于指定使用率的表空间，1为100%使用率无需整理)ASBEGIN        --创建保存分区信息的临时表        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 #ExtentInfoEND

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

0 0