February 5, 2020 at 5:29 pm
Hello,
Right off the bat, I do understand the following to be true:
That said, I think I may be missing something when it comes to understanding what needs to happen to trigger that autogrowth. If I have a log file set to 40gb in initial size, and I have a report that shows me that this log file never got to be more than 50% full, then why would it have grown? I always assumed that the log file need to be close to 100% full for the log size to increase, but it seems like the file is increasing when it hits a smaller percentage. Am I missing something, or is the report I'm relying on potentially faulty?
February 5, 2020 at 6:54 pm
Number two isn't correct though. A full backup doesn't truncate the log. In full recovery or bulk logged model, a log backup will truncate the log. In simple recovery, a checkpoint truncates the log.
Understanding the virtual log files(VLFs) and active VLFs can also affect things. This article explains the basics of that and has some pictures which makes it easier to understand:
SQL Server Transaction Log Fragmentation: a Primer
Sue
February 5, 2020 at 8:33 pm
If you are not sure what is causing the transaction log file to grow, you can find a recent history of this using data collected by the default trace, which is likely already running behind the scenes on your instance:
SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, (Duration/1000) AS Duration_sec,
td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
FROM sys.traces t
CROSS APPLY ::fn_trace_gettable(t.path, default) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE t.is_default = 1
AND td.EventClass = 93
ORDER BY td.StartTime DESC;
These results will include a column ApplicationName, which can help you identify what is doing it. If it is a SQL Agent job you will see something like "SQL Agent - TSQL JobStep Job (xxxxx)" where xxxxx is the job_id column in the table msdb.dbo.sysjobs
FYI, EventClass 93 is the Log file growth, 92 is Data file growth if you ever need that.
February 5, 2020 at 10:06 pm
Sue, thanks for the reply. I'll take a look at that article.
Chris, thanks for the info. Just to clarify, I'm fairly certain the log file always seems to grow when we run our rebuild indexes maintenance job. Those cause the biggest spikes in log usage, but they still never seem to get as high as 50% of the total log file size, which is what perplexes me with regard to the fact that the log file grows.
February 5, 2020 at 10:10 pm
For that report you use, how is it being determined that the log file was never more than 50% used at any point? I'm wondering if that's not where the issue is.
Sue
February 5, 2020 at 10:40 pm
Sue, thanks for the reply. I'll take a look at that article.
Chris, thanks for the info. Just to clarify, I'm fairly certain the log file always seems to grow when we run our rebuild indexes maintenance job. Those cause the biggest spikes in log usage, but they still never seem to get as high as 50% of the total log file size, which is what perplexes me with regard to the fact that the log file grows.
How large is the largest table in the database and what is the total used space in that database? How often are you running transaction log backups during your index rebuild process?
Are you rebuilding every index once a week or are you rebuilding only those that need to be rebuilt? And are you using reorganize any of these?
Oh - what is your log file growth set to?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2020 at 12:08 pm
Jeffrey is correct in asking the largest table size
i'd also ask.. are you doing index rebuilds or index defrags... a rebuild will take the whole index and churn it up in one big transaction (hence file growth) -I've found on occasions that a rebuild can cause t-logs to grow by up to 10 times the size of the index , (overhead of recording the transaction information)
a defrag acts in smaller transactions, but won't be as effective
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply