SQL Server 2005 事务日志太大
我正在运行 SQL Server 2005。
我的数据库备份方案是：
恢复模式：FULL
备份类型：完整
备份组件：数据库
备份集将过期：0 天后
覆盖介质：备份到现有介质集，追加到现有备份集
数据库正在写入 250GB 驱动器（实际为 232GB）。
我的 _Data.mdf 文件超过 55GB，我的 _Log.ldf 超过 148GB。
今天我们遇到了驱动器已满的情况。我将 ab_Full.bak 和 ab_Log.bak 文件移动到另一个驱动器以腾出空间 - 大约 45GB。五小时后，可用空间为 37GB。
我是管理 SQL Server 的新手；所以，我有一些关于备份的基本问题。
我知道我需要更新数据库以开始管理事务日志大小，以帮助防止将来出现此问题。因此，假设我有足够的可用空间，我：
1.右键单击数据库并选择备份
2. 将“备份类型”设置为“事务日志”
3. 将“备份集将在 30 天后过期”更改
4. 单击“确定”
我的理解是，这会将“已关闭”事务从事务日志移动到备份并截断事务日志。
这个计划合理吗？之后我需要手动调整日志文件的大小吗？
感谢您抽出时间。
I am running SQL Server 2005.
My db backup scheme is:
Recovery model: FULL
Backup Type: Full
Backup component: Database
Backup set will expire: after 0 days
Overwrite media: Back up to the existing media set, Append to the existing backup set
The db is writing to 250GB drive (232GB actual).
My _Data.mdf file is over 55GB and my _Log.ldf is over 148GB.
We ran into a situation where our drive was filled today. I moved our ab_Full.bak and ab_Log.bak files to another drive to make space - about 45GB. Five hours later, free space is at 37GB.
I'm new to managing SQL server; so, I have some basic questions about my backups.
I know I need to update the db to start managing the transaction log size to help prevent this problem in the future. So, assuming I have enough free space, I:
1. right click the db and choose Backup
2. set 'Backup Type' to 'Transaction Log'
3. change 'Backup set will expire' after to 30 days
4. click 'ok'
My understanding is this will move 'closed' transactions from the transaction log to a backup and truncate the transaction log.
Is this plan sound? Will I need to manually resize the log file afterwards?
Thanks for your time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是否随时备份事务日志？
如果您使用FULL恢复模型，那么除了备份主数据库之外，您还需要备份事务日志，或者如果您不想备份日志（那么为什么要使用FULL恢复模型呢？ ）然后至少以某个固定的时间间隔截断日志。
您应该在每次完整备份之前备份事务日志（并在保留前一个完整备份的同时保留它），以便可以恢复到自保留的第一个完整备份以来的任何时间点。此外，可能值得更频繁地备份事务日志（总大小相同），以防两次完整备份之间发生问题。
Are you backing up the transaction log at any time at all?
If you are using the FULL recovery model, then you need to back up the transaction log in addition to backing up the main database, or if you don't want to back up the log (why would you then use the FULL recovery model?) then at least truncate the log at some regular interval.
You should back up the transaction log before every full backup (and keep it as long as you keep the previous full backup) so you can restore to any point in time since the first full backup you've kept. Also, it might be worth backing up the transaction log more often (the total size is the same) in case something bad happens between two full backups.
最好的程序是定期备份日志文件。与此同时，对于像您所描述的那样的“灾难性”场景，您可以使用此代码片段来减小日志的大小：
http://www.snip2code.com /Snippet/12913/如何正确收缩 SQL 日志文件
The best procedure is to regularly backup your log file. In the mean-time, for 'catastrofic' scenarios like the one you described, you may use this snippet to reduce the size of your log:
http://www.snip2code.com/Snippet/12913/How-to-correctly-Shrink-Log-File-for-SQL