SQL Transaction log growth

Question

My transaction log file is growing each day to a very large size.

Is it the way some of my Stored Procedure and how I do inserts that could be creating the problem?

Is there a way to minimize the log size by re-writing queries?

Is there something in the query that causes the log size to grow faster?

Unless you back it up or shrink it the log file will continuously grow - it records all changes in your database. you can schedule a backup throughout the day to reset it — dbajtr, Jun 13 '17 at 14:54

score 2 · Answer 1 · answered Jun 13 '17 at 14:59

There are various considerations to investigate when log file grows bigger. I would follow step wise checklist

1) Find high impact queries , use the following 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) Once you know the logical reads/writes , you can follow up whats the impact of sp or query Optimize it.

3) You can always shrink log file, but i would say analyse the considerations in shrinking log file ( Prod environment, execution time, impact for other users)

4) If you think It will have a minimal impact in shrinking log file use this sql DBCC SHRINKFILE (N'My DB_Log' , 1000) Please Check the log file size, i wont shrink to 1. Read this blog for more info DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

score 1 · Answer 2 · answered Jun 13 '17 at 16:56

You haven't mentioned what your backup strategy is, or the recovery requirements. If you genuinely don't need to be able to restore t a point in time and would be happy with being able to restore to your regular backup point you could also consider setting the recovery mode to simple.

Probably not recommended in most scenarios, but it's a possibility.

If you do need the detailed logs, then as others have said you'll need to balance your tlog backups against file growth. If the log file is growing too large, too quickly, then back it up more frequently.

Article from RedGate: https://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/

score 0 · Answer 3 · answered Jun 13 '17 at 16:22

Please don't shrink the Transaction Log file unless there's a good reason for it. If the TLog is growing daily, the first thing you should do is increase the frequency of your TLog backups. If you're still seeing that the TLog is growing daily, you may be running into an issue preventing the vlog header from circling back to the head of the file. This often presents itself as the initial record returned by running DBCC LOGINFO having a status of 2 followed by a lot of records with a status of 0. To fix this problem you can reference my answer to a similar DBA.SE question, here.

SQL Transaction log growth

3 Answers3