Why my SQL transaction log grows so large and my disk doesn't have enough space.
来源:互联网 发布:plc编程要学多久 编辑:程序博客网 时间:2024/05/18 02:26
昨天在帮助一个客户检查他的SQL Server数据库整体情况。发现都还好,除了日子文件特别大,46G,而磁盘也就48G左右。如果日子文件因为空间不够而不能再增长了,那数据库就停止了。情况比较紧急。
按照常规步骤:
1. dbcc sqlperf(database name), 密度很高,居然是100% 看来是真的满了。
2. dbcc opentran 无返回。说明没有开着的transaction.
3. backup log <database name> with truncate_only, 报错,因为日志已满无法操作。
4. 客户反映2周之前做过全备份。这个客户还是比较敬业的,能够及时做备份。但备份后,没有把日志truncate过.
5. 再次检查日志文件设置,发现初始大小居然是46G,难过涨得这么大。即使要shrink,也是不可能减少到46G以下的。
客户同意把日志移动到另一个磁盘。我就先把数据库take offline,然后再detach下来(这是最快的方法),把日志复制到另一个磁盘上,最后attach成功,数据库重现online了。这时,但我再次用backup log <database name> with truncate_only后,检查dbcc sqlperf(database name),密度奇迹般的降低到了0.5%.
- Why my SQL transaction log grows so large and my disk doesn't have enough space.
- Why doesn't my new Favicon display?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why doesn't Windows 2000 retain my pagefile settings?
- Why Doesn't Python Have Switch/Case?
- Why doesn't Windows 2000 retain my pagefile settings?铪铪铪
- DataFormatString 的使用
- zencart建设外贸站点必须的几个插件
- 价值10W大洋的触摸屏技术揭秘。。。
- 2011-2-24
- Mark shuttleworth的工作方式值得学习
- Why my SQL transaction log grows so large and my disk doesn't have enough space.
- MFC中动态数组CArray的使用
- DECLARE_INTERFACE宏
- 字符串哈希表的一个实现
- Visual Studio 2005装AJAX Control Toolkit
- 数据库设计5步骤
- Linux sar 命令详解
- 使用opengl载入静态3d模型.obj的方法
- 当加班成为一种文化