sp_spaceused 查询后表的unused空间非常大

Question

rowsinfo reserved          datainfo          index_size         unused
639532 9766984 KB 4579624 KB 81640 KB          5105720 KB
1053160 8923136 KB 3562704 KB 155320 KB  5205112 KB
34938 539872 KB          421144 KB  672 KB          118056 KB
32636 418504 KB          392920 KB  2720 KB          22864 KB
64035 253184 KB  217192 KB  12512 KB          23480 KB也收缩了数据库，也重建了聚集索引，就是没效果，
也执行了下面的语句，
DUMP TRANSACTION [web] WITH NO_LOG 
BACKUP LOG [web] WITH NO_LOG 
DBCC SHRINKDATABASE ([web],0)请问这么大的unused正常吗？跟表内的ntext字段有关系吗？应该怎么处理。这么大的数据库磁盘IO都跟不上了

Answer 1

看看这堆文字是否有用?*--压缩数据库的通用存储过程 
  
 压缩日志及数据库文件大小 
 因为要对数据库进行分离处理 
 所以存储过程不能创建在被压缩的数据库中 --邹建 2004.03(引用请保留此信息)--*/ /*--调用示例 
 exec p_compdb  'test ' 
--*/ use master  --注意,此存储过程要建在master数据库中 
go if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1) 
drop procedure [dbo].[p_compdb] 
GO create proc p_compdb 
@dbname sysname,   --要压缩的数据库名 
@bkdatabase bit=1,   --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库 
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间 
as 
--1.清空日志 
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH  NO_LOG ') --2.截断事务日志： 
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小 
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ') --4.设置自动收缩 
exec( 'EXEC sp_dboption  ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ') --后面的步骤有一定危险,你可以可以选择是否应该这些步骤 
--5.分离数据库 
if @bkdatabase=1 
begin 
 if isnull(@bkfname, ' ')= ' '  
  set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112) 
   +replace(convert(varchar,getdate(),108), ': ', ' ') 
 select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname 
 exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ') 
end --进行分离处理 
create table #t(fname nvarchar(260),type int) 
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ') 
exec( 'sp_detach_db  ' ' '+@dbname+ ' ' ' ') --删除日志文件 
declare @fname nvarchar(260),@s varchar(8000) 
declare tb cursor local for select fname from #t where type=64 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s= 'del " '+rtrim(@fname)+ '" ' 
 exec master..xp_cmdshell @s,no_output 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb --附加数据库 
set @s= ' ' 
declare tb cursor local for select fname from #t where type=0 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' ' 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb 
exec( 'sp_attach_single_file_db  ' ' '+@dbname+ ' ' ' '+@s) 
go 
------------------------------------------------------------------------------------也可在企业管理里收缩日志： 
--收缩数据库 
dbcc shrinkdatabase( '数据库名 ',0,notruncate) 
DUMP TRANSACTION [库名] WITH NO_LOG   
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。 USE UserDB 
GO 
DBCC SHRINKFILE (DataFil1, 7) 
下例将 UserDB 用户数据库中的文件减小，以使 UserDB 中的文件有 10% 的可用空间。 DBCC SHRINKDATABASE (UserDB, 10) 
GO 

Answer 2

create procedure sys.sp_spaceused --- 2003/05/19 14:00  
@objname nvarchar(776) = null,  -- The object we want size on.  
@updateusage varchar(5) = false  -- Param. for specifying that  
     -- usage info. should be updated.  
as  
  
declare @id int   -- The object id that takes up space  
  ,@type character(2) -- The object type.  
  ,@pages bigint   -- Working variable for size calc.  
  ,@dbname sysname  
  ,@dbsize bigint  
  ,@logsize bigint  
  ,@reservedpages  bigint  
  ,@usedpages  bigint  
  ,@rowCount bigint  
  
/*  
**  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 = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)  
  
 -- Translate @id to internal-table for queue  
 IF @type = 'SQ'  
  SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue  
  
 /*  
 **  Does the object exist?  
 */  
 if @id is null  
  begin  
   raiserror(15009,-1,-1,@objname,@dbname)  
   return (1)  
  end  
  
 -- Is it a table, view or queue?  
 IF @type NOT IN ('U ','S ','V ','SQ','IT')  
 begin  
  raiserror(15234,-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.  
*/  
if @id is null  
begin  
 select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  
  , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  
  from dbo.sysfiles  
  
 select @reservedpages = sum(a.total_pages),  
  @usedpages = sum(a.used_pages),  
  @pages = sum(  
    CASE  
     -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"  
     When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
     When a.type <> 1 Then a.used_pages  
     When p.index_id < 2 Then a.data_pages  
     Else 0  
    END  
   )  
 from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
  left join sys.internal_tables it on p.object_id = it.object_id  
  
 /* unallocated space could not be negative */  
 select   
  database_name = db_name(),  
  database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))   
   * 8192 / 1048576,15,2) + ' MB'),  
  'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then  
   (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))   
   * 8192 / 1048576 else 0 end),15,2) + ' MB')  
  
 /*  
 **  Now calculate the summary data.  
 **  reserved: sum(reserved) where indid in (0, 1, 255)  
 ** data: sum(data_pages) + sum(text_used)  
 ** index: sum(used) where indid in (0, 1, 255) - data  
 ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)  
 */  
 select  
  reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),  
  data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),  
  index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),  
  unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')  
end  
  
/*  
**  We want a particular object.  
*/  
else  
begin  
 /*  
 ** Now calculate the summary data.   
 *  Note that LOB Data and Row-overflow Data are counted as Data Pages.  
 */  
 SELECT   
  @reservedpages = SUM (reserved_page_count),  
  @usedpages = SUM (used_page_count),  
  @pages = SUM (  
   CASE  
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
    ELSE lob_used_page_count + row_overflow_used_page_count  
   END  
   ),  
  @rowCount = SUM (  
   CASE  
    WHEN (index_id < 2) THEN row_count  
    ELSE 0  
   END  
   )  
 FROM sys.dm_db_partition_stats  
 WHERE object_id = @id;  
  
 /*  
 ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table  
 */  
 IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0   
 BEGIN  
  /*  
  **  Now calculate the summary data. Row counts in these internal tables don't   
  **  contribute towards row count of original table.  
  */  
  SELECT   
   @reservedpages = @reservedpages + sum(reserved_page_count),  
   @usedpages = @usedpages + sum(used_page_count)  
  FROM sys.dm_db_partition_stats p, sys.internal_tables it  
  WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;  
 END  
  
 SELECT   
  name = OBJECT_NAME (@id),  
  rows = convert (char(11), @rowCount),  
  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
  data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')  
  
end  
  
  
return (0) -- sp_spaceused  

Answer 3

重建索引看看,如果你的数据库非常非常活跃,sqlserver可能会错误的给索引分配额外的空间来应对.重建完后再shrinkfile或者shrinkdatabase