I have scenario where ldf file on SQL Server grew to more than 30GB in course of 4-5 days. As this presents potential problem with space (the HDD is a bit more than 250GB) I searched few solutions but came baffled as anything that I find on shrinking of ldf file says that it will grew again. One solution is to periodically perform this operation but I am not sure if it will be helpful in long term. Is it possible somehow to fixate maximum size of ldf file and then olders records in it would removed automatically by SQL Server?
我有一个场景,SQL Server上的ldf文件在4-5天内增长到30GB以上。由于这存在潜在的空间问题(硬盘驱动器有点超过250GB),我搜索了一些解决方案,但我感到困惑,因为我发现在缩小ldf文件时发现它会再次增长。一种解决方案是定期执行此操作,但我不确定它是否会长期有用。是否有可能以某种方式固定ldf文件的最大大小,然后其中的olders记录将被SQL Server自动删除?
If this doesn't sound good please do share some ideas on how should I manage this? What is the best course of action?
如果这听起来不太好,请分享一些关于如何管理这个问题的想法?什么是最好的行动方案?
Thanks in advance!
提前致谢!
1 个解决方案
#1
3
You've got two scenarios with this situation.
在这种情况下你有两种情况。
Scenario 1 - database is in simple recovery and there is a large process growing the transaction log. In this case, there's not much you can but leave the transaction log at the largest size and upgrade your drive space if possible.
场景1 - 数据库处于简单恢复状态,并且有一个大型进程在增加事务日志。在这种情况下,您可以将事务日志保留为最大大小,并尽可能升级您的驱动器空间。
Scenario 2 (most likely) - Your database is in full recovery mode and you don't have transaction log backups configured. A good backup practice is a daily full backup (if your database isn't too big) and an hourly transaction log backup. After you have a good full backup followed by a good transaction log backup you can shrink the transaction log.
方案2(最有可能) - 您的数据库处于完全恢复模式,并且您没有配置事务日志备份。一个好的备份做法是每日完整备份(如果您的数据库不是太大)和每小时的事务日志备份。获得良好的完整备份,然后进行良好的事务日志备份后,您可以缩小事务日志。
Here's some code that may help:
以下是一些可能有用的代码:
Shrink Transaction Log - MSDN
收缩事务日志 - MSDN
DBCC SHRINKFILE (LogicalLogName, SizeInMB)
How much of the transaction log is being used -
使用了多少事务日志 -
USE DatabaseName
GO
DBCC SQLPERF (logspace)
If you find that after you shrink the transaction log that the file size is the same, try to adjust the file size manually in the transaction log settings. Alternatively, you can set the database to simple recovery and then back to full recovery. Although the latter will break your transaction log backups until the next full backup.
如果在收缩事务日志后发现文件大小相同,请尝试在事务日志设置中手动调整文件大小。或者,您可以将数据库设置为简单恢复,然后再恢复为完全恢复。虽然后者会破坏您的事务日志备份,直到下一次完整备份。
#1
3
You've got two scenarios with this situation.
在这种情况下你有两种情况。
Scenario 1 - database is in simple recovery and there is a large process growing the transaction log. In this case, there's not much you can but leave the transaction log at the largest size and upgrade your drive space if possible.
场景1 - 数据库处于简单恢复状态,并且有一个大型进程在增加事务日志。在这种情况下,您可以将事务日志保留为最大大小,并尽可能升级您的驱动器空间。
Scenario 2 (most likely) - Your database is in full recovery mode and you don't have transaction log backups configured. A good backup practice is a daily full backup (if your database isn't too big) and an hourly transaction log backup. After you have a good full backup followed by a good transaction log backup you can shrink the transaction log.
方案2(最有可能) - 您的数据库处于完全恢复模式,并且您没有配置事务日志备份。一个好的备份做法是每日完整备份(如果您的数据库不是太大)和每小时的事务日志备份。获得良好的完整备份,然后进行良好的事务日志备份后,您可以缩小事务日志。
Here's some code that may help:
以下是一些可能有用的代码:
Shrink Transaction Log - MSDN
收缩事务日志 - MSDN
DBCC SHRINKFILE (LogicalLogName, SizeInMB)
How much of the transaction log is being used -
使用了多少事务日志 -
USE DatabaseName
GO
DBCC SQLPERF (logspace)
If you find that after you shrink the transaction log that the file size is the same, try to adjust the file size manually in the transaction log settings. Alternatively, you can set the database to simple recovery and then back to full recovery. Although the latter will break your transaction log backups until the next full backup.
如果在收缩事务日志后发现文件大小相同,请尝试在事务日志设置中手动调整文件大小。或者,您可以将数据库设置为简单恢复,然后再恢复为完全恢复。虽然后者会破坏您的事务日志备份,直到下一次完整备份。