What does it mean that a Transaction Log is Full? I have it the file set to grow 20% when needed. I have 4GBs left on the drive. How do I solve this issue permanently? Running these commands solves the issue temporarily:
事务日志是完整的是什么意思?我有文件设置在需要时增长20%。我的驱动器上还剩4GB。如何永久解决此问题?运行这些命令可以暂时解决问题:
DBCC SHRINKFILE('MyDatabase_log', 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE('MyDatabase_log', 1)
8 个解决方案
#1
7
The Transaction Log is where SQL server 'Records' every change it makes so that if something goes wrong, (From software crash to Power failure, to an asteroid strike... well maybe not an an asteroid strike), it can "recover" by "undoing" all the changes it has made, since the last consistent "CheckPoint" - back to what was that last "Consistent" state of the database... at that checkpoint. Every time a Transaction completes (or "commits"), all the changes that have been stored in the Transaction Log are flagged as "ok", and the CheckPopint marker is allowed to be moved forward to after those changes, so that a future recovery will only "undo" changes to some point after that. After this happens, all the entries in the Transaction Log from before the CheckPoint are no longer needed to recover from a system crash... but they still may be needed to recover from a Hard Disk crash, so...
事务日志是SQL服务器“记录”它所做的每一次更改的地方,这样如果出现问题,(从软件崩溃到电源故障,再到小行星打击......好吧也许不是小行星打击),它可以“恢复”通过“撤消”它所做的所有更改,因为最后一致的“CheckPoint” - 返回到该检查点的数据库的最后“一致”状态。每次事务完成(或“提交”)时,存储在事务日志中的所有更改都将标记为“ok”,并且允许CheckPopint标记在这些更改之后向前移动,以便将来恢复只会在此之后“撤消”某些方面的更改。发生这种情况后,不再需要从CheckPoint之前的事务日志中的所有条目从系统崩溃中恢复......但是他们仍然可能需要从硬盘崩溃中恢复,所以...
As the other gentleman mentioned, the "recovery model" you have set up on the server controls what happens to Transaction Log entries from before the checkpoints. In Simple Mode, they are deleted when a checkpoint occurs, but you are at risk if the main data disk crashes, because your transaction log will not contain the changes written to disk since the last backup.
正如另一位绅士所提到的,您在服务器上设置的“恢复模型”控制了在检查点之前发生的事务日志条目。在简单模式下,它们会在检查点发生时被删除,但如果主数据磁盘崩溃,则存在风险,因为事务日志不包含自上次备份以来写入磁盘的更改。
In the other recovery models, the Transaction Log entries are not deleted until you do a Backup, thus protecting you against this risk...
在其他恢复模型中,在您执行备份之前不会删除事务日志条目,从而保护您免受此风险...
So, generally, when this issue occurs, it's because the server is in one of the "normal" (not simple) recovery models set up, (Incremental or Full) and they are not doing backups... . In this case the Transaction Log just keeps Growing..., and Growing... kinda like those prostate ads on TV...
因此,通常,当出现此问题时,这是因为服务器处于设置的“正常”(非简单)恢复模型之一(增量或完全),并且它们不进行备份....在这种情况下,交易日志只是保持增长......,并且正在增长...有点像电视上的那些前列腺广告......
#2
3
It sounds like you don't have a backup strategy in place. Performing any of the backups--Full, Differential, or Transaction Log--will trunc the log with the added benefit of saving a point from which you can recover in the event of a failure. You can run the Database Maintenance Wizard to help you setup a schedule of backups to run periodically.
听起来你没有适当的备份策略。执行任何备份 - 完整备份,差异备份或事务日志 - 将截断日志,并带来额外的好处,即在发生故障时保存可以恢复的点。您可以运行“数据库维护向导”来帮助您设置定期运行的备份计划。
If you honestly don't care about your data at all (in which case, I wonder why you have a database at all), then you can set the database's Recovery Mode to "Simple," which will prevent the TLog from growing at all.
如果你真的根本不关心你的数据(在这种情况下,我想知道为什么你有一个数据库),那么你可以将数据库的恢复模式设置为“简单”,这将阻止TLog的增长。
One last thing: if you are doing bulk load operations, you might also look into changing to "Bulk-Logged" while doing the bulk operations.
最后一件事:如果您正在进行批量加载操作,您可能还会考虑在进行批量操作时更改为“Bulk-Logged”。
#3
2
You should look at SQL Server Recovery models. The short answer is to change the recovery model to "Simple", but this has implications for backup/recovery.
您应该查看SQL Server恢复模型。简短的回答是将恢复模型更改为“简单”,但这对备份/恢复有影响。
#4
2
Backup often, the transaction log is cleared out every time you back up the database.
经常备份,每次备份数据库时都会清除事务日志。
#5
2
You must backup the transaction log not just the database or the log will continue to grow until you run out of space.
您必须备份事务日志而不仅仅是数据库,否则日志将继续增长,直到您的空间不足为止。
#6
1
Another simple answer is that your backup might not be scheduled. During an upgrade cycle one of our databases backup schedule was removed from the job. The log grew until we discovered that the backup was not running.
另一个简单的答案是您的备份可能没有安排。在升级周期中,我们的一个数据库备份计划已从作业中删除。日志一直在增长,直到我们发现备份未运行。
#7
0
I wouldn't do a 20% growth rate. That can have big consequences when it needs to grow. If it ever grew to, say, 100GB it'd have to grow by 20GB on the next growth - prepare for your system to slow down rather whilst this happens... Rather, I'd set it to a fixed rate - say 100MB. Of course we don't know the current size to make a more accurate recommendation.
我不会做20%的增长率。当需要增长时,这可能会产生重大影响。如果它增长到100GB,它必须在下一次增长时增长20GB - 为你的系统做好准备,而不是在发生这种情况时......相反,我会将它设置为固定速率 - 比方说100MB 。当然,我们不知道当前的大小,以提出更准确的建议。
#8
0
There are many different ways to solve this problem. It depends on what your backup requirements are.
有许多不同的方法可以解决这个问题。这取决于您的备份要求。
The main issue is that your transaction logs are not being backed up regularly, which causes the transaction log to keep growing.
主要问题是您的事务日志没有定期备份,这会导致事务日志不断增长。
SQL Server 2005 has a Simple recovery mode (a property/option on the database itself), which I use primarily in DEV and TEST environments where hourly snapshots are not required, the transaction log only grows enough to handle the biggest transaction run on the server. No schedules or maintenance plans are required for this recovery mode.
SQL Server 2005具有简单恢复模式(数据库本身的属性/选项),我主要在DEV和TEST环境中使用,其中不需要每小时快照,事务日志仅增长到足以处理服务器上运行的最大事务。此恢复模式不需要任何计划或维护计划。
In SQL Server 2000, you basically had a scheduled backup script that ran the same command you used, hourly or so:
在SQL Server 2000中,您基本上有一个计划的备份脚本,它运行您使用的相同命令,每小时左右:
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
使用TRUNCATE_ONLY备份日志MyDatabase
For production environments, typically we have an hourly Transaction Log backup and a daily Full backup scheduled in the database maintenance plans. This keeps the transaction log truncated to a reasonable size (a size that holds 1 hour worth of transaction data, obviously).
对于生产环境,通常我们在数据库维护计划中安排每小时的事务日志备份和每日完整备份。这样可以将事务日志截断为合理的大小(显然,这个大小可以容纳1小时的事务数据)。
#1
7
The Transaction Log is where SQL server 'Records' every change it makes so that if something goes wrong, (From software crash to Power failure, to an asteroid strike... well maybe not an an asteroid strike), it can "recover" by "undoing" all the changes it has made, since the last consistent "CheckPoint" - back to what was that last "Consistent" state of the database... at that checkpoint. Every time a Transaction completes (or "commits"), all the changes that have been stored in the Transaction Log are flagged as "ok", and the CheckPopint marker is allowed to be moved forward to after those changes, so that a future recovery will only "undo" changes to some point after that. After this happens, all the entries in the Transaction Log from before the CheckPoint are no longer needed to recover from a system crash... but they still may be needed to recover from a Hard Disk crash, so...
事务日志是SQL服务器“记录”它所做的每一次更改的地方,这样如果出现问题,(从软件崩溃到电源故障,再到小行星打击......好吧也许不是小行星打击),它可以“恢复”通过“撤消”它所做的所有更改,因为最后一致的“CheckPoint” - 返回到该检查点的数据库的最后“一致”状态。每次事务完成(或“提交”)时,存储在事务日志中的所有更改都将标记为“ok”,并且允许CheckPopint标记在这些更改之后向前移动,以便将来恢复只会在此之后“撤消”某些方面的更改。发生这种情况后,不再需要从CheckPoint之前的事务日志中的所有条目从系统崩溃中恢复......但是他们仍然可能需要从硬盘崩溃中恢复,所以...
As the other gentleman mentioned, the "recovery model" you have set up on the server controls what happens to Transaction Log entries from before the checkpoints. In Simple Mode, they are deleted when a checkpoint occurs, but you are at risk if the main data disk crashes, because your transaction log will not contain the changes written to disk since the last backup.
正如另一位绅士所提到的,您在服务器上设置的“恢复模型”控制了在检查点之前发生的事务日志条目。在简单模式下,它们会在检查点发生时被删除,但如果主数据磁盘崩溃,则存在风险,因为事务日志不包含自上次备份以来写入磁盘的更改。
In the other recovery models, the Transaction Log entries are not deleted until you do a Backup, thus protecting you against this risk...
在其他恢复模型中,在您执行备份之前不会删除事务日志条目,从而保护您免受此风险...
So, generally, when this issue occurs, it's because the server is in one of the "normal" (not simple) recovery models set up, (Incremental or Full) and they are not doing backups... . In this case the Transaction Log just keeps Growing..., and Growing... kinda like those prostate ads on TV...
因此,通常,当出现此问题时,这是因为服务器处于设置的“正常”(非简单)恢复模型之一(增量或完全),并且它们不进行备份....在这种情况下,交易日志只是保持增长......,并且正在增长...有点像电视上的那些前列腺广告......
#2
3
It sounds like you don't have a backup strategy in place. Performing any of the backups--Full, Differential, or Transaction Log--will trunc the log with the added benefit of saving a point from which you can recover in the event of a failure. You can run the Database Maintenance Wizard to help you setup a schedule of backups to run periodically.
听起来你没有适当的备份策略。执行任何备份 - 完整备份,差异备份或事务日志 - 将截断日志,并带来额外的好处,即在发生故障时保存可以恢复的点。您可以运行“数据库维护向导”来帮助您设置定期运行的备份计划。
If you honestly don't care about your data at all (in which case, I wonder why you have a database at all), then you can set the database's Recovery Mode to "Simple," which will prevent the TLog from growing at all.
如果你真的根本不关心你的数据(在这种情况下,我想知道为什么你有一个数据库),那么你可以将数据库的恢复模式设置为“简单”,这将阻止TLog的增长。
One last thing: if you are doing bulk load operations, you might also look into changing to "Bulk-Logged" while doing the bulk operations.
最后一件事:如果您正在进行批量加载操作,您可能还会考虑在进行批量操作时更改为“Bulk-Logged”。
#3
2
You should look at SQL Server Recovery models. The short answer is to change the recovery model to "Simple", but this has implications for backup/recovery.
您应该查看SQL Server恢复模型。简短的回答是将恢复模型更改为“简单”,但这对备份/恢复有影响。
#4
2
Backup often, the transaction log is cleared out every time you back up the database.
经常备份,每次备份数据库时都会清除事务日志。
#5
2
You must backup the transaction log not just the database or the log will continue to grow until you run out of space.
您必须备份事务日志而不仅仅是数据库,否则日志将继续增长,直到您的空间不足为止。
#6
1
Another simple answer is that your backup might not be scheduled. During an upgrade cycle one of our databases backup schedule was removed from the job. The log grew until we discovered that the backup was not running.
另一个简单的答案是您的备份可能没有安排。在升级周期中,我们的一个数据库备份计划已从作业中删除。日志一直在增长,直到我们发现备份未运行。
#7
0
I wouldn't do a 20% growth rate. That can have big consequences when it needs to grow. If it ever grew to, say, 100GB it'd have to grow by 20GB on the next growth - prepare for your system to slow down rather whilst this happens... Rather, I'd set it to a fixed rate - say 100MB. Of course we don't know the current size to make a more accurate recommendation.
我不会做20%的增长率。当需要增长时,这可能会产生重大影响。如果它增长到100GB,它必须在下一次增长时增长20GB - 为你的系统做好准备,而不是在发生这种情况时......相反,我会将它设置为固定速率 - 比方说100MB 。当然,我们不知道当前的大小,以提出更准确的建议。
#8
0
There are many different ways to solve this problem. It depends on what your backup requirements are.
有许多不同的方法可以解决这个问题。这取决于您的备份要求。
The main issue is that your transaction logs are not being backed up regularly, which causes the transaction log to keep growing.
主要问题是您的事务日志没有定期备份,这会导致事务日志不断增长。
SQL Server 2005 has a Simple recovery mode (a property/option on the database itself), which I use primarily in DEV and TEST environments where hourly snapshots are not required, the transaction log only grows enough to handle the biggest transaction run on the server. No schedules or maintenance plans are required for this recovery mode.
SQL Server 2005具有简单恢复模式(数据库本身的属性/选项),我主要在DEV和TEST环境中使用,其中不需要每小时快照,事务日志仅增长到足以处理服务器上运行的最大事务。此恢复模式不需要任何计划或维护计划。
In SQL Server 2000, you basically had a scheduled backup script that ran the same command you used, hourly or so:
在SQL Server 2000中,您基本上有一个计划的备份脚本,它运行您使用的相同命令,每小时左右:
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
使用TRUNCATE_ONLY备份日志MyDatabase
For production environments, typically we have an hourly Transaction Log backup and a daily Full backup scheduled in the database maintenance plans. This keeps the transaction log truncated to a reasonable size (a size that holds 1 hour worth of transaction data, obviously).
对于生产环境,通常我们在数据库维护计划中安排每小时的事务日志备份和每日完整备份。这样可以将事务日志截断为合理的大小(显然,这个大小可以容纳1小时的事务数据)。