sqlserver drop image类型的列不释放空间问题处理

一、 背景

drop了sqlserver一个大表image类型的列，感觉作为一个ddl语句应该在drop之后会释放空间，但drop后查询表发现并没有缩小，也不像oracle删除表空间中的表后可以复用原空间，插入数据之后数据文件还在不断增大。

 

二、 如何让sqlserver drop列后释放空间

 

法一：重建聚集索引（推荐）

1）重建聚集索引

ALTER INDEX PK_Testtab on Testtab  REBUILD With (FillFactor = 90 , Online= On);

测试1T表重建聚集索引约15分钟，可以在线REBUILD，期间不阻塞业务。重建完聚集索引后，表变小了，数据文件使用率变低了，但文件大小不变还是1T，需要手动收缩一下。

 

2）shrink数据文件

测试此过程数据库可写，收缩时间大概为2分钟

USE [Testdb]
GO
DBCC SHRINKFILE (N'Testdb' , 40000)
GO

但是要注意一下，Shrink后碎片可能会很高

 

3）再次重建聚集索引

这次是为了避免Shrink后碎片率过高

ALTER INDEX PK_Testtab on Testtab  REBUILD With (FillFactor = 90 , Online= On);

 

法二：将数据插入新表后drop原表

适用于删除的列非常大而剩余数据量少的情况，但是会影响业务，需要停机。

 

法三：DBCC CLEANTABLE（不推荐）

注意该命令全程锁表，大表执行时间非常长（1T表执行25小时还未完成）。感觉比较鸡肋，大表长时间锁表影响业务；小表没啥必要非要回收空间；而且还有类型限制。

• 对于变长列：支持 varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, xml 类型
• 对于定长列：DBCC CLEANTABLE命令不会释放空间

 

1）创建测试表

CREATE TABLE [dbo].[TestTable](
       [ID] [int] NULL,
       [Name] [varchar](100) NULL,
       [Address] [varchar](1000) NULL
) ON [PRIMARY]

insert一些测试数据

 

2）drop列前后分别记录page和record数

SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed');

ALTER TABLE [TestTable] DROP COLUMN [Address];

SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed');

结果如下，可以看到删除列后并没有变化

 

3）执行DBCC CLEANTABLE

DBCC CLEANTABLE ('dbname', 'TestTable', 0)

再次执行前面的查询，可以看到page变小了非常多。

 

三、drop列发生了什么

drop列的操作不影响现有数据，这只是一个元数据操作。可以使用视图sys.system_internals_partition_columns查看，可以看到is_dropped=1表示列没有消失，只是被标记为已删除：

CREATE TABLE NAME (
    id INT IDENTITY PRIMARY KEY,
    first CHAR(1000) NOT NULL,
    middle CHAR(1000) NOT NULL,
    last CHAR(1000) NOT NULL
);

INSERT NAME (first, middle, last) VALUES ('Michael', 'J', 'Swart');
INSERT NAME (first, middle, last) VALUES ('Lester', 'B', 'Pearson');
INSERT NAME (first, middle, last) VALUES ('Mack', 'D', 'Knife');
INSERT NAME (first, middle, last) VALUES ('Homer', 'J', 'Simpson');

ALTER TABLE NAME DROP COLUMN Middle;

INSERT NAME (First, Last) VALUES ('Bartholomew', 'Simpson');
INSERT NAME (First, Last) VALUES ('Lisa', 'Simpson');

查看被删除列信息

select t.name as tablename,
c.name as columnname,
    ipc.*
from sys.system_internals_partition_columns ipc
join sys.partitions p
    on ipc.partition_id = p.partition_id
join sys.tables t
    on t.object_id = p.object_id
left join sys.columns c
    on c.object_id = t.object_id
    and c.column_id = ipc.partition_column_id
where t.name = 'NAME'
order by c.column_id

 

查看日志记录

SELECT MAX ([Current LSN]) FROM fn_dblog (null, null); -- 00000599:0000783a:000a

ALTER TABLE NAME DROP COLUMN Last;

SELECT [Page ID],[Current LSN],Operation,Context,AllocUnitName,[Lock Information],Description FROM fn_dblog (null, null) WHERE [Current LSN] > '00000599:0000783a:000a';

输出如下：

Page ID	Current LSN	Operation	Context	AllocUnitName	Lock Information	Description
NULL	00000599:00007842:0001	LOP_BEGIN_XACT	LCX_NULL	NULL	NULL	2019/08/14 11:50:08:203;ALTER TABLE;0x01
NULL	00000599:00007842:0002	LOP_LOCK_XACT	LCX_NULL	NULL	HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 6:18099105:0
0001:00000039	00000599:00007842:0003	LOP_MODIFY_ROW	LCX_SCHEMA_VERSION	sys.syscolpars.clst
0001:000000e8	00000599:00007842:0004	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysallocunits.clust	NULL	Action 0 (HOBTCOUNT) on rowset 72057594044547072. Leaf page count: 3, Reserved page count: 9, Used page count: 5
0001:000000a1	00000599:00007842:0005	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysrowsets.clust	NULL	Action 1 (ROWSETCOUNT) on rowset 72057594044547072. Row count: 6.
0001:00000045	00000599:00007842:0006	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysrscols.clst	NULL	Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 1, mod count: 6
0001:00000045	00000599:00007842:0007	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysrscols.clst	NULL	Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 2, mod count: 6
0001:00000045	00000599:00007842:0008	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysrscols.clst	NULL	Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 3, mod count: 6
0001:00000045	00000599:00007842:0009	LOP_COUNT_DELTA	LCX_CLUSTERED	sys.sysrscols.clst	NULL	Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 4, mod count: 6
NULL	00000599:00007842:000a	LOP_HOBT_DDL	LCX_NULL	NULL	NULL	Action 2 (ALTER_HOBT) on HoBt 0x65:100, partition 0x0, rowset 72057594044547072.
0001:00000045	00000599:00007842:000b	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	sys.sysrscols.clst	HoBt 196608:ACQUIRE_LOCK_IX OBJECT: 6:3:0 ;ACQUIRE_LOCK_X KEY: 6:196608 (02af0b2cf9fe)
0001:00000001	00000599:00007842:000c	LOP_MODIFY_HEADER	LCX_PFS	Unknown Alloc Unit	NULL	Field m_typeFlagBits
0001:00000001	00000599:00007842:000d	LOP_SET_BITS	LCX_PFS	sys.sysrscols.clst
NULL	00000599:00007842:000e	LOP_HOBT_DDL	LCX_NULL	NULL	NULL	Action 2 (ALTER_HOBT) on HoBt 0x65:100, partition 0x0, rowset 72057594044547072.
0001:00000045	00000599:00007842:000f	LOP_INSERT_ROWS	LCX_CLUSTERED	sys.sysrscols.clst	HoBt 196608:ACQUIRE_LOCK_IX OBJECT: 6:3:0 ;ACQUIRE_LOCK_X KEY: 6:196608 (02af0b2cf9fe)
0001:00000001	00000599:00007842:0010	LOP_SET_BITS	LCX_PFS	sys.sysrscols.clst
0001:0000012b	00000599:00007842:0011	LOP_MODIFY_ROW	LCX_CLUSTERED	sys.sysschobjs.clst	HoBt 281474978938880:ACQUIRE_LOCK_IX OBJECT: 6:34:0 ;ACQUIRE_LOCK_X KEY: 6:281474978938880 (bc1f56567263)
0001:000000b1	00000599:00007842:0012	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	sys.syscolpars.nc	HoBt 562949956108288:ACQUIRE_LOCK_IX OBJECT: 6:41:0 ;ACQUIRE_LOCK_X KEY: 6:562949956108288 (5db225c69ef8)
0001:00000001	00000599:00007842:0013	LOP_SET_BITS	LCX_PFS	sys.syscolpars.nc
0001:00000039	00000599:00007842:0014	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	sys.syscolpars.clst	HoBt 281474979397632:ACQUIRE_LOCK_IX OBJECT: 6:41:0 ;ACQUIRE_LOCK_X KEY: 6:281474979397632 (6e51f765b719)
0001:00000001	00000599:00007842:0015	LOP_SET_BITS	LCX_PFS	sys.syscolpars.clst
NULL	00000599:00007842:0016	LOP_LOCK_XACT	LCX_NULL	NULL	HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 6 PERMISSIONS(class = 1, major_id = 18099105), lockPartitionId = 0
NULL	00000599:00007842:0017	LOP_COMMIT_XACT	LCX_NULL	NULL	NULL	2019/08/14 11:50:08:210
0001:00000039	00000599:00007848:0001	LOP_EXPUNGE_ROWS	LCX_CLUSTERED	sys.syscolpars.clst	NULL
0001:00000001	00000599:00007848:0002	LOP_SET_BITS	LCX_PFS	sys.syscolpars.clst
0001:000000b1	00000599:00007848:0003	LOP_EXPUNGE_ROWS	LCX_INDEX_LEAF	sys.syscolpars.nc	NULL
0001:00000001	00000599:00007848:0004	LOP_SET_BITS	LCX_PFS	sys.syscolpars.nc
0001:00000001	00000599:00007848:0005	LOP_MODIFY_HEADER	LCX_PFS	Unknown Alloc Unit	NULL	Field m_typeFlagBits

各Operation含义

操作	含义
LOP_ABORT_XACT	Indicates that a transaction was aborted and rolled back.
LOP_BEGIN_CKPT	A checkpoint has begun.
LOP_BEGIN_XACT	Indicates the start of a transaction.
LOP_BUF_WRITE	Writing to Buffer.
LOP_COMMIT_XACT	Indicates that a transaction has committed.
LOP_COUNT_DELTA
LOP_CREATE_ALLOCCHAIN	New Allocation chain
LOP_CREATE_INDEX	Creating an index.
LOP_DELETE_ROWS	Rows were deleted from a table.
LOP_DELETE_SPLIT	A page split has occurred. Rows have moved physically.
LOP_DELTA_SYSIND	SYSINDEXES table has been modified.
LOP_DROP_INDEX	Dropping an index.
LOP_END_CKPT	Checkpoint has finished.
LOP_EXPUNGE_ROWS	Row physically expunged from a page, now free for new rows.
LOP_FILE_HDR_MODIF	SQL Server has grown a database file.
LOP_FORGET_XACT	Shows that a 2-phase commit transaction was rolled back.
LOP_FORMAT_PAGE	Write a header of a newly allocated database page.
LOP_HOBT_DDL
LOP_HOBT_DELTA
LOP_IDENT_NEWVAL	Identity’s New reseed values
LOP_INSERT_ROWS	Insert a row into a user or system table.
LOP_LOCK_XACT
LOP_MARK_DDL	Data Definition Language change – table schema was modified.
LOP_MARK_SAVEPOINT	Designate that an application has issued a ‘SAVE TRANSACTION’ command.
LOP_MIGRATE_LOCKS
LOP_MODIFY_COLUMNS	Designates that a row was modified as the result of an Update command.
LOP_MODIFY_HEADER	A new data page created and has initialized the header of that page.
LOP_MODIFY_ROW	Row modification as a result of an Update command.
LOP_PREP_XACT	Transaction is in a 2-phase commit protocol.
LOP_SET_BITS
LOP_SET_BITS	Designates that the DBMS modified space allocation bits as the result of allocating a new extent.
LOP_SET_FREE_SPACE	Designates that a previously allocated extent has been returned to the free pool.
LOP_SORT_BEGIN	A sort begins with index creation. – SORT_END end of the sorting while creating an index.
LOP_SORT_EXTENT	Sorting extents as part of building an index.
LOP_UNDO_DELETE_SPLIT	The page split process has been dumped.
LOP_XACT_CKPT	During the Checkpoint, open transactions were detected.

参考

https://wikidba.net/tag/fn_dblog-operations-details/

https://michaeljswart.com/2010/02/removing-columns/

https://solutioncenter.apexsql.com/zh/%E8%AF%BB%E5%8F%96%E4%B8%80%E4%B8%AAsql-server%E4%BA%8B%E5%8A%A1%E6%97%A5%E5%BF%97/

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-showcontig-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-201

https://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/27/alter-table-drop-column-does-not-reclaim-the-space-the-column-took-it-s-a-meta-data-change-only.aspx

https://www.sentryone.com/blog/aaronbertrand/drop-column-fix

http://www.sqldbadiaries.com/2011/03/03/space-used-does-not-get-changed-after-dropping-a-column/

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql?view=sql-server-2017