巨大的交易日志 - 这正常吗? [英] Huge Transaction Log - Is This Normal?

问题描述

我有一个 5GB 的数据库和一个 20GB 的事务日志 (SQL Server 2005).不知道为什么它这么大或发生了什么使它这么大，它曾经是数据库大小的 1/2 左右.DB 增长约 1GB/月.

I have a 5GB database and a 20GB transaction log (SQL Server 2005). Not sure why it is so large or what happened to make it that large, it used to be around 1/2 the size of the DB. DB grows about 1GB/month.

对于您的事务日志相对于您的数据库文件大小应该有多大，是否有任何指导方针?

Are there any guidelines to how how big your transaction log should be relative to your database file size?

我并不是说我的事务日志很大(我知道有些 DBA 会嘲笑我的小型数据库)，只是与我认为它很大的 DB 文件有关.

I'm not saying my transaction log is huge (I know some DBAs would laugh at my weenie-sized DB), just in relation to the DB file I think it is huge.

推荐答案

呃...请原谅明显的流血，但是您是否有使用BACKUP LOG"的计划备份

Er... excuse the bleeding obvious, but do you have scheduled backups with "BACKUP LOG"

如果恢复模式为 FULL，那么这需要发生.

If the recovery model is FULL then this needs to happen.

我将包括其他一些罕见的选项(并非详尽无遗):

There are other, rare options that I'll include (not exhaustive):

• 大表索引重建/维护.但是，备份日志会清除这一点.
• 阻止备份日志删除条目的开放事务(因此它增长)
• 与 SET IMPLICIT_TRANSACTIONS ON 的连接(见上一点)

• Large table index rebuild/maintenance. However, backup log will clear this.
• An open transaction preventing backup log removing entries (hence it grows)
• A connection with SET IMPLICIT_TRANSACTIONS ON (see previous point)

这篇关于巨大的交易日志 - 这正常吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！