SQL Server 2005事务日志太大 [英] SQL Server 2005 Transaction Log too big

问题描述

我正在运行SQL Server 2005.

I am running SQL Server 2005.

我的数据库备份方案是:
恢复模型:FULL
备份类型:完整
备份组件:数据库
备份集将过期:0天后
覆盖媒体:备份到现有媒体集，追加到现有备份集

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

数据库正在写入250GB驱动器(实际为232GB).

The db is writing to 250GB drive (232GB actual).

我的_Data.mdf文件超过55GB，我的_Log.ldf超过148GB.

My _Data.mdf file is over 55GB and my _Log.ldf is over 148GB.

今天我们陷入了困境.我将ab_ Full.bak和ab _Log.bak文件移动到另一个驱动器上以腾出空间-大约45GB.五小时后，可用空间为37GB.

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.

我是管理SQL Server的新手.因此，我对备份有一些基本问题.

I'm new to managing SQL server; so, I have some basic questions about my backups.

我知道我需要更新数据库以开始管理事务日志大小，以帮助将来防止此问题.因此，假设我有足够的可用空间，我:
1.右键单击数据库，然后选择备份
2.将备份类型"设置为事务日志"
3.将备份集将过期"更改为30天
4.点击确定"

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?

感谢您的时间.

推荐答案

您是否随时备份事务日志? 如果使用的是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.

这篇关于SQL Server 2005事务日志太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！