SQL Server 删除大量数据锁死

在项目中做sql 的优化，有一张表，里面有几千万条记录，查询速度很慢，就想到了分表。

分表后要删除原表中多余的数据，就粗暴的用了 delete from table_name where ldate<'2014-01-01'

执行了2个小时也没有结束，就cancel了这个request， 然后就发现再次查询表就被卡死，一直没有response。又重启了机器，还是不行。

 

     使用下面的语句查看被锁的表：
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName  
from   sys.dm_tran_locks where resource_type='OBJECT'

再用 sp_who 查看是什么操作锁的表，最后悲剧的发现是 “DB STARTUP” 锁死。

 

从这篇文档中找到： http://stackoverflow.com/questions/3385314/master-database-db-startup-problem

原来我取消 delete, 数据库要做rollback 操作，所以锁表在rollback. 而且只能一直等待它结束。

 

最后再回到原来的目的，删除大量数据，不能直接用delete， 可以考虑 把数据 selete * into 复制到新的表，再truncate 原表， 然后再改回来表名。（注意：select * into 不会复制表的索引）.  truncate 不记录日志，不产生rollback.

 

 PS:  做任何操作都不能直接在生产环境做，一定要在测试环境上测试之后才能执行。

 

PS:等了一个半小时，终于解锁了。还不死心，再次用delete from 删除150W记录，执行了28分钟。

总结，删除 百万以上的记录就要小心了，会耗时很久。