SQL事务日志增长

Question

我的事务日志文件每天都增长到非常大的大小。

是不是我的一些存储过程和我做插入的方式可能会造成问题？

有没有办法通过重写查询来最小化日志大小？

查询中是否存在导致日志大小增长更快的原因？

Answer 1

当日志文件变大时，需要考虑的因素有很多。我会按照步骤检查清单

1)查找高影响查询，使用以下SQL

SELECT TOP 5 t.TEXT AS 'SQL Text'
    ,st.execution_count
    ,ISNULL(st.total_elapsed_time / st.execution_count, 0) AS 'AVG Excecution Time'
    ,st.total_worker_time / st.execution_count AS 'AVG Worker Time'
    ,st.total_worker_time
    ,st.max_logical_reads
    ,st.max_logical_writes
    ,st.creation_time
    ,ISNULL(st.execution_count / DATEDIFF(second, st.creation_time, getdate()), 0) AS 'Calls Per Second'
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
ORDER BY st.total_elapsed_time DESC

2)一旦您了解了逻辑读写，您可以跟踪sp的影响或查询优化它。

3)您总是可以收缩日志文件，但我想分析收缩日志文件时的注意事项(生产环境、执行时间、对其他用户的影响)

4)如果您认为使用此sql DBCC SHRINKFILE (N'My DB_Log' , 1000)收缩日志文件的影响最小请检查日志文件大小，我不会收缩到1。请阅读此博客以了解更多信息DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Answer 2

您没有提到您的备份策略是什么，也没有提到恢复要求。如果您确实不需要能够恢复到某个时间点，并且对能够恢复到常规备份点感到满意，那么您还可以考虑将恢复模式设置为简单。

在大多数情况下可能不推荐使用，但这是有可能的。

如果您确实需要详细的日志，那么就像其他人所说的那样，您需要平衡tlog备份和文件增长。如果日志文件变得太大、太快，请更频繁地备份它。

来自RedGate的文章：https://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/

Answer 3

请不要收缩事务日志文件，除非有充分的理由。如果TLog每天都在增长，您应该做的第一件事就是提高TLog备份的频率。如果您仍然看到TLog每天都在增长，那么您可能遇到了阻止vlog头循环回到文件头的问题。这通常是通过运行状态为2的DBCC LOGINFO返回的初始记录，然后是许多状态为0的记录。要解决此问题，您可以参考类似的DBA.SE问题here的答案。