博客

周四晚上要把测试数据库结构备份一份周五到客户方部署到客户的测试服务器上。

之前备份一个库一般几分钟十几分钟吧，⏰18点下班的我决定17点停掉测试站点开始备份数据，嘻嘻~

二十分钟后数据备份完成，看了下文件大小20G，怎么拷到客户机，而且也不应该有这么大的数据，于是开始下面的表演~

1.查看库中每个表的数据量

SELECT  a.name AS 表名 ,
        MAX(b.rows) AS 记录条数
FROM    sys.sysobjects AS a
        INNER JOIN sys.sysindexes AS b ON a.id = b.id
WHERE   ( a.xtype = 'u' )
GROUP BY a.name
ORDER BY 记录条数 DESC; 

发现有张表有九千五百万数据

看看这张表里都是什么数据：SELECT TOP 100 * FROM RoomStatus_Dump

啊~难受? 。一个SELECT执行了3分钟。

检查发现有2011年的数据，只有6千条2019年的数据是需要的，2019年以前的数据全删掉吧?

使用truncate大概1秒就搞定了，好嗨哟~使用delete，但在删除时我可以根据条件时间小于2019年吗？（上面查询100条用了三分钟）

于是决定时间倒叙查询后按月删吧，删了几次后总结出来规律，每删除1千万数据（6个月数据量）大概需要十分钟

算了一下：（2019-2011）* 2 * 10=160分钟；去领个盒饭吧~?

我觉得可能这张表的索引碎片膨胀了

2.检查表中的索引碎片

--检查表的索引碎片情况
DECLARE @table_id INT;
SET @table_id = OBJECT_ID('RoomStatus_Dump');
--执行
DBCC SHOWCONTIG(@table_id);

解释如下：

Page Scanned-扫描页数

：

如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。

Extents Scanned-扫描扩展盘区数

：

用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

Extent Switches-扩展盘区开关数

：

该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

Avg. Pages per Extent-每个扩展盘区上的平均页数

：

该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。

Scan Density [Best Count:Actual Count]-扫描密度［最佳值:实际值］

：

DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

Logical Scan Fragmentation-逻辑扫描碎片

：

无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

Extent Scan Fragmentation-扩展盘区扫描碎片

：

无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。

Avg. Bytes Free per Page-每页上的平均可用字节数

：

所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。

Avg. Page Density (full)-平均页密度（完整）

：

每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片

3.清除索引碎片

有很多方法，我这里使用删除重建索引?

--删除索引
DROP INDEX RoomStatus_Dump.idx_RoomStatus_Dump_hotelid
DROP INDEX RoomStatus_Dump.PK_RoomStatus_Dump

--聚集索引
create CLUSTERED INDEX PK_RoomStatus_Dump ON RoomStatus_Dump(字段, 字段, 字段)
--创建非聚集索引
create NONCLUSTERED INDEX idx_RoomStatus_Dump_hotelid ON  RoomStatus_Dump(字段)

再来看下索引碎片情况，完美~?

4.报错了“数据库事务日志已满”

DELETE是DML，执行DELETE操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

好吧删除日志，查看日志存放位置：右键数据库→属性→文件

日志文件已经80G了?

5.删除日志文件

数据库属性→选项 恢复模式设置为简单

右键数据库→任务→收缩→文件

再检查下日志只有几兆了，删完记得把恢复模式再改回完整。

删除后备份文件500M，能接受。

⌚19:50

?

溜了溜了

6.彩蛋

将表生成脚本，创建新的表，将6千条需要的数据插入到新表，然后truncate掉旧表，将新表改名为旧表的表名。

十分钟搞定?