2

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.

enter image description here

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.

  • Is the transaction log file size at 7GB, but the used size much smaller? Try DBCC SQLPERF(LOGSPACE) – Forrest Oct 31 '17 at 16:04
  • @Forrest that's correct and that is what make me believe there is/are a chuncky transaction(s) going on. the log % in use is 0.1291157 – Marcello Miorelli Oct 31 '17 at 16:07
  • Do you have access to historical log backups? Can you see anything unusual in their sizes? – Forrest Oct 31 '17 at 16:15
1

Try this extended event (change the filename path accordingly). I've used it in the past to help me track down what was causing unexpected growth in my data and log files.

CREATE EVENT SESSION [DB Size Tracking] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\DB Size Tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

You'll need to shrink your log on the TEST server, then let the jobs run so that it grows again. This extended event will then log the SQL text of what causes the autogrowth event.

  • this is very useful, more and more I feel like using this extended events just been a bit reluctant – Marcello Miorelli Nov 2 '17 at 12:45
  • 1
    It took me a while to start using extended events too. I still don't use them as often as I could. Erin Stellato has a good "stairways" series on SQL Server Central sqlservercentral.com/stairway/134867 if you want to start working with them more. – Eric Cobb Nov 2 '17 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.