背景：

我们有些表，会每天定时地去刷新数据，由于数据量过大，需要先删除原有的数据，然后再添加新的数据进去，SQL的机制是在此过程中会造成大量空间浪费（日志、碎片）

我们尝试了用DBCC命令去进行空间释放，但是都没有什么效果，十几万的数据，占了1个G的空间，这太夸张了。

使用的释放空间语句：

DBCC shrinkfile(file_id) -收缩日志
DBCC shrinkdatabase (N'db1') -收缩
DBCC CleanTable(dbname,'tablename',0) -释放已删除数据的空间

 

查询总体的空间使用情况：

exec sp_spaceused 

查询每张表的空间使用情况：

/* Drop the temp table if it's there from a previous run                     */
/*—————————————————————————*/
if object_id(N'tempdb..[#TableSizes]') is not null
  drop table #TableSizes ;
go
 
/*—————————————————————————*/
/* Create the temp table                                                     */
/*—————————————————————————*/
create table #TableSizes
  (
    [Table Name] nvarchar(128)   /* Name of the table */
  , [Number of Rows] char(11)    /* Number of rows existing in the table. */
  , [Reserved Space] varchar(18) /* Reserved space for table. */
  , [Data Space] varchar(18)    /* Amount of space used by data in table. */
  , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */
  , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */
  ) ;
go
 
/*—————————————————————————*/
/* Load the temp table                                                        */
/*—————————————————————————*/
declare @schemaname varchar(256) ;
-- Make sure to set next line to the Schema name you want!
set @schemaname = 'dbo' ;
 
-- Create a cursor to cycle through the names of each table in the schema
declare curSchemaTable cursor
  for select sys.schemas.name + '.' + sys.objects.name
      from    sys.objects
            , sys.schemas
      where   object_id > 100
              and sys.schemas.name = @schemaname
              /* For a specific table uncomment next line and supply name */
              --and sys.objects.name = 'specific-table-name-here'    
              and type_desc = 'USER_TABLE'
              and sys.objects.schema_id = sys.schemas.schema_id ;
 
open curSchemaTable ;
declare @name varchar(256) ;  /* This holds the name of the current table*/
 
-- Now loop thru the cursor, calling the sp_spaceused for each table
fetch curSchemaTable into @name ;
while ( @@FETCH_STATUS = 0 )
  begin    
    insert into #TableSizes
            exec sp_spaceused @objname = @name ;       
    fetch curSchemaTable into @name ;   
  end
 
/* Important to both close and deallocate! */
close curSchemaTable ;     
deallocate curSchemaTable ;
 
 
/*—————————————————————————*/
/* Feed the results back                                                     */
/*—————————————————————————*/
select [Table Name]
      , [Number of Rows]
      , [Reserved Space]
      , [Data Space]
      , [Index Size]
      , [Unused Space]
from    [#TableSizes]
order by CONVERT(int, SUBSTRING( [Reserved Space],0,PATINDEX('% %',[Reserved Space]))) desc;
 
/*—————————————————————————*/
/* Remove the temp table                                                     */
/*—————————————————————————*/
drop table #TableSizes ;

 

目前，由于经过测试，DBCC命令无法释放浪费的空间。

 

 
当前测试结果表明，就算删的只剩下几条，也不会释放浪费的空间。只有表在数据删光后，才会清空释放所有的空间。

我们当前只想到了一个方法就是将表翻新一下。

所以我们做了一个引擎，可以定时创建一个新的表，将原表数据导入进去，然后再将原表删除，将新表的名称改为原表的名称。

效果：