长时间运行SSIS的ETL,造成SSISDB数据库过大撑爆磁盘,如何处理?

浏览: 3463

不想听唠叨的请直接右边【目录】-【解决办法】。

年底事儿特别多!!!

之前有同事学 @biwork 老师的课程,顺手就拿过来看了看视频(没学完,嘿嘿),然后由于公司需要,当时就用SSIS的ETL做了一套关于数据处理的报表系统。整个系统的架构一句话、一张图就说完了:把分公司的服务器数据拉过来,结合报表系统,集中处理为业务要求的报表展现。

系统架构

整体的系统架构如下图:

QQ20170106-103928@2x.png

服务器配置就是windows 2008 r2 sp1,硬件配置(最初只是用来做测试用的)如下:

QQ20170106-112236@2x.png

ETL包的结构如下图,每个子包里面的控制流有5个左右,每个控制流中是不同复杂程度的数据流(单个流涉及多个库抽取表、字段类型转换、追加字段、与系数表做计算后汇总,排序……)。

QQ20170106-104402@2x.png

运行情况

整个ETL开发完成在2015年3月份上线,最初是单个包,拿所有15家公司的数据,所以存在单点故障,一旦一个公司网络挂掉(挂的时间点、公司都是随机性的),整个包都拿不到数据。所以年底开发离职后我又重新设计了包的架构,把每个公司弄成单个包,然后由统一的父包调用子包,消除单点故障。

稳定运行近2年时间,期间除了偶尔加一两个单独的包,基本没什么变动。

故障出现

刚好年底,涉及组织架构的调整,然后需要对ETL包做修改和增加,登上去后发现服务器异常慢(开发ETL是在服务器上完成的),然后打开SQL查看执行情况的报表(等了将近3分钟,才出数据),一看吓了一跳,最右边一列是【持续时间(秒)】,夸张吧?

QQ20170106-105229@2x.png

刚好运维发现这台服务器的D盘(数据库所在)可用空间只剩下3G左右,而占用空间最大的就是SSISDB.mdf(30G)、SSISDB.ldf(150G)两个文件:

SSISDB.ldf.png

相当于整个库的大小在180G左右,明显不正常,所以找到我,开始商量对策。

故障排查(套路)

由于运维不清楚是否有业务数据,所以按照套路(百度),分别做了以下的几点尝试:

  1. 收缩SSISDB数据库,直接报异常(我估计是库太大引起的);
  2. 收缩SSISDB文件,没有什么效果(只降低了3G的空间);
  3. 修改SSISDB.ldf日志文件名(系统会自动创建一个.ldf文件,只有预置的几十MB大小),然后运维让我尝试再执行ETL作业。

到这个阶段,磁盘空间释放了,ETL虽然可以执行(部署),但依然要用故障所列的那么长时间。

论坛求助

自己找解决办法的同时,分别在天善论坛(https://ask.hellobi.com/question/22758)、MSDN论坛(https://social.msdn.microsoft.com/Forums/zh-CN/6d4a8e81-87c4-4aa0-b51b-44ddc1148bac/etlssisdbmdf?forum=sqlserverzhchs)发贴求助,同时给@biwork老师也发了邮件。

同时我也在尝试:因为自己很清楚这个库是没有任何业务数据的,所以做了以下的一些尝试:

  1. 分离SSISDB.mdf数据库,发现ETL作业无法执行了,同时【Integration Services目录】无法列表目录结构,SSDT部署包也提示找不到SSIS目录
  2. 恢复SSISDB.mdf数据库,导出该库的结构创建脚本,打算重新建一个空的具有ssisdb结构的库,结果坑爹的是:执行这个脚本卡死了,然后打开脚本检查,才发现里面创建脚本时会默认设置文件大小为导出库的大小,也就是30G。。。(基本功不扎实啊)
  3. 设置SSISDB.mdf的恢复模式为简单;
  4. 查看SSIS Server Maintenance Job任务的执行情况,一切正常;

共中3、4是在隔了一天后,发现没有任何效果。

解决办法

参考MSDN论坛的zjcxc.邹建给的思路:先查这个库的真实占用大小,再找对应表的问题,再找解决办法。表的真实大小结果可以去那边的贴子查看。

实际他还是没有明确说怎么处理,所以我就自作主张(因为库的占用是真实的),找到其中最大的一张表:

exec sp_MSforeachtable "exec sp_spaceused '?',true"

QQ20170105-135521@2x.png

执行delete命令删除所有记录,下班前执行的,没管它,今天早上来的时候听说执行完了,就过去运行了一下ETL作业(包括SSDT的包部署),并让业务部门出了报表,一切正常!!!

注意:删除前,我也查了这张表的数据,稍做了判断才干的哈。而且就算删了恢复不了,大不了重装SQL Server,重新部署包;哪怕再不行,马上让运维弄一台临时数据库服务器,部署ETL包上去,也能保证业务出报表。(但这些套路都是下下策,因为故障可能重现,而这些套路太费时间了,不是我的风格,哈哈)

现在SSIS的库文件只有300多MB大小,ETL包的执行时间也恢复正常了:

QQ20170106-110326@2x.png QQ20170106-124529@2x.png

这个库的大小为什么会缩减这么多?我也不清楚,因为当时搞运维的在我直接delete前,尝试网上的清空库的方法,发现很多表都有外键关系,执行要报错。

一点感想

我喜欢简单粗爆的办法,但有个前提就是思路、场景得交待清楚,看我之前发的一篇博文,血的教训啊(http://www.cnblogs.com/sfrost/p/4884443.html)。

所以没有参考百度出来的那篇文章(http://www.cnblogs.com/luck001221/p/4312784.html),因为很多东西没有交待,而且风险太大了(打补丁呀,多危险的事)。。。

发贴目的

引用BIWORK老师的话,就是“记录一下,供其他遇到同样问题的朋友参考。”

投石问路(个人思考)

这个SSISDB.mdf数据库,应该是负责SSIS的ETL包管理(存放路径、控件的属性和值,以及执行的日志记录等信息),但由于我每次执行的包记录太多(一个消息记录的【概述】有3页,【详细报表】有近40页,每页近20条记录),所以导致了这个库的增长太快:

12.png

简单的办法就是写个定时任务,去清理这张表(event_message_context),但毕竟感觉不太正式。。。。。。

推荐 6
本文由 sfrost 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

数据库的基本原理还是基础啊
sfrost

sfrost 回复 seng

嘿嘿,就是,而且还要对这个库有所了解才行。

要回复文章请先登录注册