首先看一个例子：

--创建测试表

CREATE TABLE testfreespace

( column1 INT

,column2 CHAR(20)

,column3 VARCHAR(8000))

--插入数据

DECLARE @count INT;

SET @count = 0;

WHILE @count < 3000

BEGIN

SELECT

@count = @count + 1;

INSERT into testfreespaceVALUES ( @count,'test row # '+ CAST(@countAS VARCHAR(10)),REPLICATE('TestData', 3000)) ;

END

3. --查看使用空间使用情况

SELECT

alloc_unit_type_desc,

page_count,

avg_page_space_used_in_percent,

record_count

FROM sys.dm_db_index_physical_stats(DB_ID('FNDBLogtest'),OBJECT_ID(N'Testfreespace'),NULL,NULL,'Detailed')

alloc_unit_type_desc page_countavg_page_space_used_in_percent record_count

-------------------------------------------------------------------------- --------------------

IN_ROW_DATA 3000 99.27106498640973000

(1 row(s) affected)

4. --删除整张表数据