Transaction log growing out of control on random times

Question

We have a problem with one of our databases. At random times something causes our transaction log to swell up to 20x its original size (it basically fills up the whole HD, so 20x is not accurate since it might grow more if there was space left on disk)

We do full backup once a week and differentials every day. Our database is using SIMPLE recovery mode. I am trying to go through the transaction log of the differential and full backup files using fn_dblog and fn_dump_dblog, yet for whatever reason, in both cases it is showing me [Begin Time] and [End Time] for a very small amount of time. For differential it is showing records for a time span of only around 1/2 a minute. For the full backup, it is showing records within a span of 5-10 minutes.

Here is the query I was running, hoping from here i can find long running transaction logs from the past:

SELECT 
    [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName   
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, 'DB_backup_path',
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a
WHERE 
    Operation in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')
order by 
    [Transaction ID]

Here is the query I was running to see long transaction which are still in activate transaction log:

SELECT 
    [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName
FROM 
    fn_dblog(NULL, NULL) 
WHERE [Operation] in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')

In both cases, why am I getting data only for a small span of time?

Also, on more generic level, is this the best way to investigate why on random times the transaction log grows out of control. We looked at all the reoccurring sql jobs we are running and there is nothing extra ordinary there. When we kick them off, they run for at most 5 minutes. Even running multiple jobs at the same time causes no bottlenecks. Our application does not use any transactions when it talks to DB, so we are completely confused.

Any ideas?

score 0 · Answer 1 · answered Jun 06 '18 at 19:09

If you are running in simple recovery mode, then the only transaction log data the full and differential backups will contain are for the very narrow time while the backup itself is in process, so looking at that is not going to help you here.

To see real-time activity, you can instead run the SQL Profiler.

Before I did that, though, I'd try a few other ways to identify open transactions:

Download and use sp_WhoIsActive, look at the open_tran_count and perhaps the start_time columns.
Run a query to show explicit and implicit open transactions.

Something like:

SELECT st.session_id, DB_NAME(sp.dbid) as dbname, at.name, 
    at.transaction_begin_time, sp.last_batch, sp.status, 
    RTRIM(sp.hostname) as hostname, RTRIM(sp.program_name) as program, 
    RTRIM(sp.cmd) as command, RTRIM(sp.loginame) as loginname
FROM sys.dm_tran_active_transactions at
INNER JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
INNER JOIN sys.sysprocesses sp ON st.session_id = sp.spid
ORDER BY transaction_begin_time

If you see something that has been running for a very long time, try doing a kill and see if the log clears up.

Transaction log growing out of control on random times

1 Answers1