2

There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of this process and it's working fine. By now, most people know how and why Transaction Log works. There is a complete explanation here.

I have a misbehaving application that, somehow, create a growing transaction log by not releasing, not sure if it's not committing or not closing or what, at the end of the (whatever) action.

It doesn't look like there is data loss or inconsistencies and not any unusual situation on the DB.

Upon restarting SQLSERVER show the database in 'recovery mode' for a while and then back to normal.

Doing DBCC LOGINFO, shows over 100 entries, all of them with 'Status=2' so I understand, they are in use. Interesting but on some entries, the 'filesize' is even larger that the Database itself!

The subject database on SELECT name,log_reuse_wait_desc,recovery_model_desc FROM sys.databases show log_reuse_wait_desc as 'NOTHING' and recovery_model_desc as 'FULL'

My question is: Is there any method, documented or not, to 'read' the log file and find what table, application, data or whatever info is on the VLF to help finding the culprit application?

fcm
  • 121
  • 3
  • Might be a dup of https://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space – BradC Apr 21 '17 at 15:22
  • 1
    How big are the VLFs? Sounds like you've got some big ones, so the finished transactions in those won't clear until the entire VLF can clear. – Tara Kizer Apr 21 '17 at 15:44

1 Answers1

0

Are you running transaction log backups?

  • If so, you may need to increase their frequency.
  • If not, you need to either start taking them, or switch to SIMPLE recovery mode.

If you are taking frequent tran log backups, and the logs are still growing, you might have a problem with bad application code, if they never properly commit/close transactions. In that case have your developers (or the vendor) fix the app.

You can use a query like this to find all open transactions:

Select st.session_id, st.is_user_transaction, st.is_local, 
    at.transaction_type, at.transaction_state,
    sp.dbid, db_name(sp.dbid) as dbname, sp.cpu, sp.physical_io, 
    sp.login_time, sp.last_batch, sp.open_tran, 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 at.transaction_begin_time ASC

EDIT: Some further thoughts, based on your comments:

  1. Make sure you are using a fixed growth size, for a large database I usually use 2000MB, 4000MB, or 8000MB, depending on what I expect the total size to be.
  2. Follow Kim's instructions here to shrink and then re-grow the log, so you have reasonably-sized VLFs.
  3. Is this database involved in replication? Was it ever?
  4. Is rebooting the server the only time the log clears? Do tran log backups clear it?
  5. When was the last full backup? You need a proper full to "restart" the log backup chain.

Run the following query to distinguish between used and free space within the log file, and monitor it throughout the day:

 SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;
BradC
  • 9,964
  • 8
  • 48
  • 88
  • @fcm Daily? In environments I've worked, we do full backups nightly, and transaction log backups hourly, or even as often as every 15 min on very high activity databases. Increasing that seems the most obvious thing to try. If that still doesn't work, start investigating VLF size per Kim's blog here: https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/ – BradC Apr 21 '17 at 16:29
  • Yes, transaction log backup daily, VLC size from 2 to 12 gb, the DB is about 8gb),transaction log can grow up to 200gb in just a couple of days. Your query show just on transaction and in a different database – fcm Apr 21 '17 at 16:30
  • 2-12GB VLF size for an 8GB database? There is no culprit application, you've got a VLF issue. – Tara Kizer Apr 21 '17 at 16:49
  • You're only backing up the transaction log once a day? It's pretty common these days to back it up every 1-5 minutes even. – Tara Kizer Apr 21 '17 at 16:50