I have a database of 20 GB which insists in having its transaction log over 7GB.
when I used this script to find out the size of the biggest objects in that database, I see they are relatively small.
I have been using the default trace to see when this transaction log has been autogrown, but I found none.
DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1; SELECT DatabaseName, [FileName], SPID, Duration, StartTime, EndTime FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass = 93 -- log autogrow event ORDER BY StartTime DESC;
I have also tried some scripts from here, to check what transactions are filling the log, but could not find any.
I believe there must be some very long transactions going on at the same time, or at least one of more long transactions being executed.
How can I check for these (
long transactions in the database)?
In LIVE this is a full recovery mode database, part of alwayson. In TEST this is a simple recovery mode database, but because all the jobs are there, the log grows to 7GB all the same.