首先看一个例子：
--创建测试表
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(@countASVARCHAR(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. --删除整张表数据
delete from testfreespacewith(TABLOCK)
5. 运行脚本3得到下面的
alloc_unit_type_desc page_count avg_page_space_used_in_percentrecord_count
-------------------------------------------------------------------------------- ------------------------------ --------------------
IN_ROW_DATA 3000 0 0
可以看到虽然整张表的数据都被删除了但是表空间数据没有被释放。原因是SQL Server HEAP表空间释放需要两个条件：
· A deletion onthis table occurs.
· A table-levellock is being held.
释放空页时，数据库中的其他对象将无法重用关联的空间。
解决这个问题可以用下面的方法：
· 在 DELETE语句中指定 TABLOCK提示。使用 TABLOCK 提示会导致删除操作获取表的共享锁，而不是行锁或页锁。这将允许释放页。
· 如果要从表中删除所有行，请使用 TRUNCATE TABLE。
· 删除行之前，请对堆创建聚集索引。删除行之后，可以删除聚集索引。与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。
下面我用DELETE WITH (TABLOCK)删除表然后我们看一下结果：
--删除整张表数据
delete fromtestfreespacewith(TABLOCK)
alloc_unit_type_desc page_count avg_page_space_used_in_percentrecord_count
-------------------------------------------------------------------------------- ------------------------------ --------------------
IN_ROW_DATA 0 0 0
另外建议表建立主键。（上面只针对HEAP表的情况）。
本文转自 lzf328 51CTO博客，原文链接:
http://blog.51cto.com/lzf328/960311
