不想听唠叨的请直接右边【目录】-【解决办法】。
年底事儿特别多!!!
之前有同事学 @biwork 老师的课程,顺手就拿过来看了看视频(没学完,嘿嘿),然后由于公司需要,当时就用SSIS的ETL做了一套关于数据处理的报表系统。整个系统的架构一句话、一张图就说完了:把分公司的服务器数据拉过来,结合报表系统,集中处理为业务要求的报表展现。
系统架构
整体的系统架构如下图:
服务器配置就是windows 2008 r2 sp1,硬件配置(最初只是用来做测试用的)如下:
ETL包的结构如下图,每个子包里面的控制流有5个左右,每个控制流中是不同复杂程度的数据流(单个流涉及多个库抽取表、字段类型转换、追加字段、与系数表做计算后汇总,排序……)。
运行情况
整个ETL开发完成在2015年3月份上线,最初是单个包,拿所有15家公司的数据,所以存在单点故障,一旦一个公司网络挂掉(挂的时间点、公司都是随机性的),整个包都拿不到数据。所以年底开发离职后我又重新设计了包的架构,把每个公司弄成单个包,然后由统一的父包调用子包,消除单点故障。
稳定运行近2年时间,期间除了偶尔加一两个单独的包,基本没什么变动。
故障出现
刚好年底,涉及组织架构的调整,然后需要对ETL包做修改和增加,登上去后发现服务器异常慢(开发ETL是在服务器上完成的),然后打开SQL查看执行情况的报表(等了将近3分钟,才出数据),一看吓了一跳,最右边一列是【持续时间(秒)】,夸张吧?
刚好运维发现这台服务器的D盘(数据库所在)可用空间只剩下3G左右,而占用空间最大的就是SSISDB.mdf(30G)、SSISDB.ldf(150G)两个文件:
相当于整个库的大小在180G左右,明显不正常,所以找到我,开始商量对策。
故障排查(套路)
由于运维不清楚是否有业务数据,所以按照套路(百度),分别做了以下的几点尝试:
- 收缩SSISDB数据库,直接报异常(我估计是库太大引起的);
- 收缩SSISDB文件,没有什么效果(只降低了3G的空间);
- 修改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老师也发了邮件。
同时我也在尝试:因为自己很清楚这个库是没有任何业务数据的,所以做了以下的一些尝试:
- 分离SSISDB.mdf数据库,发现ETL作业无法执行了,同时【Integration Services目录】无法列表目录结构,SSDT部署包也提示找不到SSIS目录;
- 恢复SSISDB.mdf数据库,导出该库的结构创建脚本,打算重新建一个空的具有ssisdb结构的库,结果坑爹的是:执行这个脚本卡死了,然后打开脚本检查,才发现里面创建脚本时会默认设置文件大小为导出库的大小,也就是30G。。。(基本功不扎实啊)
- 设置SSISDB.mdf的恢复模式为简单;
- 查看SSIS Server Maintenance Job任务的执行情况,一切正常;
共中3、4是在隔了一天后,发现没有任何效果。
解决办法
参考MSDN论坛的zjcxc.邹建给的思路:先查这个库的真实占用大小,再找对应表的问题,再找解决办法。表的真实大小结果可以去那边的贴子查看。
实际他还是没有明确说怎么处理,所以我就自作主张(因为库的占用是真实的),找到其中最大的一张表:
exec sp_MSforeachtable "exec sp_spaceused '?',true"
执行delete命令删除所有记录,下班前执行的,没管它,今天早上来的时候听说执行完了,就过去运行了一下ETL作业(包括SSDT的包部署),并让业务部门出了报表,一切正常!!!
注意:删除前,我也查了这张表的数据,稍做了判断才干的哈。而且就算删了恢复不了,大不了重装SQL Server,重新部署包;哪怕再不行,马上让运维弄一台临时数据库服务器,部署ETL包上去,也能保证业务出报表。(但这些套路都是下下策,因为故障可能重现,而这些套路太费时间了,不是我的风格,哈哈)
现在SSIS的库文件只有300多MB大小,ETL包的执行时间也恢复正常了:
这个库的大小为什么会缩减这么多?我也不清楚,因为当时搞运维的在我直接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条记录),所以导致了这个库的增长太快:
简单的办法就是写个定时任务,去清理这张表(event_message_context),但毕竟感觉不太正式。。。。。。